行中的第一个空单元格 [英] First Empty cell in row

问题描述

我正在将范围复制到另一个工作簿的下一个空单元格中。以下代码；

I am copying a range into the next empty cell of a different workbook. the following code;

Public Sub InvoicedInstallments()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim fname As String
Dim mt As String, yr As String
mt = MonthBox.Value
yr = YearBox.Value
fname = yr & mt & "DB" & ".xlsx"
Set rng1 = Workbooks("201209TB.xlsx").Worksheets("excel").Range("E348")
Set rng2 = Workbooks("201209TB.xlsx").Worksheets("excel").Range("E295")
Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Cells(1, Columns.Count).End(xlToRight).Select

rng3.Value = (rng1.Value + rng2.Value)
rng3.Value = rng3.Value * -1
rng3.Value = Round(rng3.Value / 1000, 0)

End Sub

有行

Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Cells(1, Columns.Count).End(xlToRight).Select

已更改为

Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Range("AA49").End(xlToRight).Offset(0, 1)

这现在会将数据放入下一个空单元格（这是我想要的），但是如果出现以下情况，则不会将信息放入下一个空单元格您再次运行代码。

this will now put the data into the next empty cell (which is what I wanted) but it does not then put information into the next empty cell if you run the code again.

我想这只是简单的事，但我找不到它

I imagine it is just something simple but I can't spot it

它正在复制到合并的单元格中，我怀疑它占有很多的问题
（请参阅以下评论以确认这一点）

It is copying into merged cells which I suspect holds a lot of issues (see below comments for confirmation of this)

推荐答案

我猜，您的问题将在此行：

I am guessing, your problem will be in this line:

rng3.Value = Round(rng3.Value / 1000, 0)

但是这里抛出错误

Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Cells(1, Columns.Count).End(xlToRight).Select

是因为 select 。

选择，选择它并不会返回范围对象。

Select, selects, it does not return you a range object.

请改用

Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Cells(1, Columns.Count).End(xlToRight)

但是，我不确定这是否是您想要的，因为您可能只需说

However, I am not sure, if this is what you want, because you might just say

Set rng3 = Workbooks(fname).Worksheets("UK monthly dashboard").Range("IV1")

因为这就是您得到的-至少，如果您仅使用 .xlsx

because this is what you get - at least, if you are working only with .xlsx

这篇关于行中的第一个空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！