提问人：ak40837 提问时间：7/13/2019 最后编辑：Pᴇʜak40837 更新时间：7/24/2019 访问量：556
如何在循环中引用单元格？
How to reference a cell in a loop?
问：
我在寻找字符串“Yes”时正在循环一行。如果找到字符串，我想将单元格中的值复制到其左侧的一列中，然后将其粘贴到另一个工作表中。
我正在努力解决的代码部分是引用单元格偏移包含字符串“Yes”的单元格。代码不就是这样吗？ActiveCell.Offset(-1,0)
我可能以错误的方式思考这个问题，所以我对建议持开放态度。
Sub This_One_Will_Work()
Dim Y_N_Column As Integer, LastColumn As Integer, CurrentRow As Integer, LastRow As Integer, New_Market_Tracker As Range
'need ActiveRow as variable and for it to go up 3 times
Y_N_Column = 2
Last_Open_Row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count
Last_Tracker_Row = ThisWorkbook.Worksheets("Market Tracker").UsedRange.Rows.Count
Set New_Market_Tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1")
'ActiveCell = cell with the offset the "Yes" cell in the Y_N_Column
Do While Y_N_Column <= Last_Open_Row
If ThisWorkbook.Worksheets("Market to Open").Cells(1, Y_N_Column).Value = "Yes" Then
ActiveCell.Offset(0, -1).Copy Sheet6.Range("A1").Offset(Last_Tracker_Row, 0)
'End If
End If
Y_N_Column = Y_N_Column + 1
Loop
End Sub
答：
1赞
AAA
7/13/2019
#1
Sub This_One_Will_Work()
Dim Y_N_Column As Long, Last_Tracker_Row As Long, Last_Open_Row As Long
Dim LastRow As Integer, New_Market_Tracker As Range
Y_N_Column = 2
Last_Open_Row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count
Last_Tracker_Row = ThisWorkbook.Worksheets("Market Tracker").UsedRange.Rows.Count
Set New_Market_Tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1")
Do While Y_N_Column <= Last_Open_Row
With ThisWorkbook.Worksheets("Market to Open")
If .Cells(1, Y_N_Column).Value = "Yes" Then
Sheet6.Cells(Last_Tracker_Row+1, 1).Value = .Cells(1, Y_N_Column-1).Value
End If
End With
Y_N_Column = Y_N_Column + 1
Loop
End Sub
评论
0赞
ak40837
7/15/2019
你自己尝试过这个代码吗？我试了一下，但没有任何运气。不过，我不明白为什么你的代码不起作用。
0赞
AAA
7/15/2019
@ak40837，它没有理由不起作用。你犯了什么错误，在哪条线上？
0赞
ak40837
7/16/2019
没有错误，只是没有出现字符串。可能是我这边解决的问题。
0赞
AAA
7/16/2019
@ak40837你的声明可能是错误的。如果你看一下你的问题，你会发现你使用的变量与你声明的变量不同。为什么不单步执行代码以查看问题所在？例如，什么是？Y_N_Column
1赞
BruceWayne
7/13/2019
#2
您需要 VBA 吗？您可以使用以下公式：
=INDEX($A$2:$A$7,SMALL(IF(B$2:B$7="Yes",ROW(A$2:A$7)-ROW(A$2)+1),ROWS(A$2:A2)))
其中范围是要拉取的数据，范围是“是”的位置：AB
输入数组公式并向下拖动。CTRL+SHIFT+ENTER
（您可以将该公式包装起来，以隐藏在用完匹配项时显示的错误。IFERROR([formula],"")#NUM!
评论
0赞
ak40837
7/15/2019
感谢您的输入，您的公式有效。但是，我认为VBA是这个项目的更好解决方案。
0赞
ak40837
7/24/2019
#3
这是我试图实现的目标的答案：
Sub For_Each_SKA()
Dim last_open_column As Long
Dim new_market_tracker As Range
Dim last_tracker_row As Long
Dim last_open_row As Long
last_open_column = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Columns.Count
Set new_market_tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1")
last_open_row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count
For c = 1 To last_open_column
last_tracker_row = ThisWorkbook.Worksheets("Market Tracker").Range("A" & Rows.Count).End(xlUp).Row
If ThisWorkbook.Worksheets("Markets to Open").Cells(1, c).Value = "SKA" Then
new_market_tracker.Copy ThisWorkbook.Worksheets("Market Tracker").Range("A" & last_tracker_row + 3)
End If
For r = 2 To last_open_row
last_tracker_row = ThisWorkbook.Worksheets("Market Tracker").Range("A" & Rows.Count).End(xlUp).Row
If ThisWorkbook.Worksheets("Markets to Open").Cells(r, c + 1).Value = "Yes" Then
ThisWorkbook.Worksheets("Market Tracker").Range("A" & last_tracker_row + 1).Value = _
ThisWorkbook.Worksheets("Markets to Open").Cells(r, c).Value
End If
Next r
Next c
End Sub
评论
Cells(1, Y_N_Column-1).Value会起作用;类似于使用 Active Cell 列出的 U，但不需要列中除 -1 之外的任何内容offsetActiveCell