Excel 2010 VBA，使用 vlookup 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14735788/
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
Excel 2010 VBA , looping with vlookup
提问by schnarr
I having issues with using vlookup in my Excel 2010 VBA
我在 Excel 2010 VBA 中使用 vlookup 时遇到问题
I want to loop through each cell in a column that is blank and look up a value from the neighbouring column from a data source. I want to do this in a loop. I have looked in many forums and can get this to work properly.
我想遍历空白列中的每个单元格，并从数据源的相邻列中查找值。我想循环执行此操作。我查看了许多论坛，可以使其正常工作。
heres a sample of my code
这是我的代码示例
Sheets("NPT(hrs)").Activate
Dim NumberRows As Integer
NumberRows = Range("a:a").Find("*", Range("A1"), SearchDirection:=xlPrevious).Row
NumberRows = NumberRows
ActiveCell.Offset(2, 69).Activate
For RowNum = 1 To NumberRows
If Len(Range("BQ1").Offset(RowNum, 0)) = 0 Then
ActiveCell.Value = Application.WorksheetFunction.VLookup(Cells(RowNum, 68), "Equipment!A:K", 7, False)
ActiveCell.Offset(1, 0).Activate
'' This approach never workedXXX
'v = WorksheetFunction.VLookup(Cells(RowNum, 68), "Equipment!A:K", 7, False)
' ActiveSheet.Cells(RowNum , 69).Formula = "=VLOOKUP(Cells(RowNum,68) ,Equipment!A:K,7,false)"
'ActiveCell.Text = "v"
'MsgBox "fin work?"
End If
回答by JConnor
Here is a quick code block that I wrote to replicate what I think you're trying to get at.
这是我编写的一个快速代码块，用于复制我认为您想要获得的内容。
I created some sample data. The lookup on the right has unit prices and on the left you have a table with some entries missing in the 'total price' column.
我创建了一些示例数据。右侧的查找有单价，左侧有一个表格，其中“总价”列中缺少一些条目。
The code block below loops through the cells in column C and runs a vlookup each time it finds a blank cell.
下面的代码块循环遍历 C 列中的单元格，并在每次找到空白单元格时运行一次 vlookup。
Sub FillInBlanks()
Dim rng As Range
Set rng = ActiveSheet.Range("C2:C21")
For Each cell In rng
If IsEmpty(cell) Then
cell.FormulaR1C1 = "=VLOOKUP(RC[-2],R1C7:R11C8,2,FALSE)*RC[-1]"
End If
Next cell
End Sub
Running this macro should give you this
运行这个宏应该会给你这个
