For 循环 - 如何设置范围从活动行到最后一行？-解网

问：

我已经尝试了许多不同的方法来做到这一点，但似乎无法让它工作，即使它看起来应该很容易做到。我希望我的 For 循环从（并包括）活动行中查看每一行，直到最后一行，其中包含数据，但不包括活动行上方行中的数据。然后，它将复制此范围内邮政编码与第一个活动行相同的任何行，并将其粘贴到新工作簿中。我使用的原始代码是从第二行到最后一行，其余的代码都适用于此，所以这不是问题。请参阅下面的代码。

'Any extra measures need to be copied using an if loop, this is so it will capture all rows underneath with the same postcode.
'I want the range to be from the active cell, down to the last row. I am having trouble setting this.

Dim r As Range
Dim j As Integer
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate

'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range("J2:J" & lastRow1)
'This copies all of the rows that match within the whole workbook, need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

然后我尝试调整范围以从仅活动单元格行到最后一行，我尝试了几种不同的方法，我不明白为什么这不起作用：

'Any extra measures need to be copied using an if loop, this is so it will capture all rows underneath with the same postcode.
'I want the range to be from the active cell, down to the last row. I am having trouble setting this.

Dim r As Range
Dim j As Integer
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate

'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range(ActiveCell.row & lastRow1)
'This copies all of the rows that match within the whole workbook, need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

当我将鼠标悬停在VBA窗口中的ActiveCell.row上时，它显示分配给它的正确行号，并且lastRow1变量也正确显示为15。

任何建议都非常感谢，对于我在这里发布并习惯礼仪的任何错误，我深表歉意！:)

谢谢

Excel VBA 循环 for 循环

应该怎么做？它将范围与单元格值进行比较，这似乎没有意义。看起来很奇怪，你说每个范围在一个范围内？你不需要最后吗？而且我认为实际的范围没有正确定义，你定义了一个盒子，所以你需要 2 对 Col 字母和行号。iffor each.rows()

0赞 Liv Hudson 9/18/2023

@taller_ExcelHome 这很有效，谢谢！:)

答：

0赞 Black cat 9/18/2023 #1

若要指定行范围，必须将范围设置为字符串。

像这样的东西


For Each r In wb.Worksheets("Sheet1").Range("""" & ActiveCell.row & ":" & lastRow1 & """")

它会抛出错误，因为这只会迭代行。如果要迭代每个单元格，则必须添加 Cells 属性，如下所示

For Each r In wb.Worksheets("Sheet1").Range("""" & ActiveCell.row & ":" & lastRow1 & """").Cells

'Any extra measures need to be copied using an if loop, this is so it will capture all rows underneath with the same postcode.
'I want the range to be from the active cell, down to the last row. I am having trouble setting this.

Dim r As Range
Dim j As Long
Dim post_code_on_sheet As String
post_code_on_sheet = wb2.Worksheets("Sheet2").Cells(3, 2).Value
lastRow1 = wb.Worksheets("Sheet1").Range("J" & rows.Count).End(xlUp).row
j = 2
Cells(row, 10).Activate 'what is row here?
'if row isn't defined as a proper variable, this will throw an error
'DEFINE RANGE AS FROM ACTIVE ROW DOWN TO LAST NON-EMPTY ROW
For Each r In wb.Worksheets("Sheet1").Range("J" & row & ":J" & lastRow1)
'This copies all of the rows that match within the whole workbook, need it to select only from the active row down
   If r = post_code_on_sheet Then
       wb.Worksheets("Sheet1").rows(r.row).Copy wb2.Worksheets("Sheet1").rows(j)
       j = j + 1
    End If
Next r

如果您只想要这些值，则无需复制粘贴。

编辑：没有注意到你在此期间得到了帮助，哎呀。

For 循环 - 如何设置范围从活动行到最后一行？

For Loop - How Can I Set The Range For To Be From The Active Row Down To Last Row?

评论

评论

评论