在 Excel 中使用 VBA，如何遍历行并设置单元格值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14530985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using VBA in Excel, how do I loop through rows and set cell values?
提问by Jeff Brady
I'm trying to teach myself some VBA. I have a workbook with 2 worksheets, Sheet1 and Sheet2. I found this code below and modified it to work for me. Gets the value of Sheet1!B1, looks for it in Sheet2!K:K, and sets Sheet1!D1 to the value of a cell in column E (K - 6) of Sheet2.
我正在尝试自学一些 VBA。我有一个包含 2 个工作表的工作簿，Sheet1 和 Sheet2。我在下面找到了这段代码并对其进行了修改以适合我。获取 Sheet1!B1 的值，在 Sheet2!K:K 中查找，并将 Sheet1!D1 设置为 Sheet2 的 E (K - 6) 列中单元格的值。
How can I loop this so it looks at B1:B100 and sets D1:D100?
我如何循环它以便它查看 B1:B100 并设置 D1:D100？
Sub Looping()
Dim rng1 As Range
Dim strSearch As String
strSearch = Range("B1").Value
Set rng1 = Worksheets("Sheet2").Range("K:K").Find(strSearch, , xlValues, xlWhole)
If Not rng1 Is Nothing Then
Range("D1").Value = rng1.Offset(0, -6)
Else
MsgBox strSearch & " not found"
End If
End Sub
Thanks!
谢谢！
回答by Peter Albert
First of all, you don't need a VBA solution for that problem. Simply put this formula in Sheet1!D1:D100:
首先，您不需要针对该问题的 VBA 解决方案。只需将此公式放在 Sheet1!D1:D100 中：
=IFERROR(INDEX(Sheet2!$E:E$,MATCH(B1,Sheet2!$K:$K,0))),"Not found")
In case you want to do this with VBA, here is your code in a loop:
如果你想用 VBA 做到这一点，这里是你的循环代码：
Sub Looping()
Dim rngTarget As Range
Dim rngSearched as Range
For Each rngTarget in Sheets("Sheet1").Range("D1:D100")
Set rngSearched = Sheets("Sheet2").Range("K:K").Find( _
rngTarget.Offset(, -2).Value, , xlValues, xlWhole)
If rngSearch Is Nothing Then
rngTarget.Value = "not found"
Else
rngTarget.Value = rngSearch.Offset(, -6)
End If
Next rngTarget
End Sub
Alternatively, combine both approaches:
或者，结合两种方法：
Sub FillDirectly
With Sheets("Sheet1").Range("D1:D100")
.Formula="=IFERROR(INDEX(Sheet2!$E:E$,MATCH(B1,Sheet2!$K:$K,0))),""Not found"")"
.Calculate
.Value = .Value 'Creates a value copy
End With
End Sub
