如何通过Find函数获取行

Question

我试图循环一个范围的单元格，然后检查范围rngIng中是否每个单元格都匹配。如果有，获取匹配的行，然后复制B列中偏移单元格的值，并将其值粘贴到J列中。

当我试图获取该行时，出现了一个变量类型错误。

以下是问题所在：row = .rngIng.Find(What:=Name, LookIn:=xlFormulas, LookAt:=xlWhole).row。

Set ws = ThisWorkbook.Worksheets(1)
'Find which is the last row with data in column "C"
' Define range with data in column "C"
Dim rngIng As Range
Dim lastRowIng As Integer
With ws.Columns("C")
lastRowIng = .Cells(.Rows.Count).End(xlUp).row
Set rngIng = .Range(Cells(2, "C"), Cells(lastRowIng, "C"))
End With

'Find which is the last row with data in column "A"
Dim lastRowRef As Integer
With ws.Columns("A")
lastRowRef = .Cells(.Rows.Count).End(xlUp).row
End With

Dim i, j, row As Long
i = 2
j = 2

For i = 2 To lastRowRef
Dim Name As Variant
Name = ws.Cells(i, "A").Value
With ws
row = .rngIng.Find(What:=Name, LookIn:=xlFormulas, LookAt:=xlWhole).row
End With
If row <> 0 Then
ws.Cells(j, "J").Value = ws.Cells(row, "B").Value
j = j + 1
End If
Next
End Sub 

Answer 1

试试这个：

Sub Tester()

Dim ws As Worksheet, j As Long
Dim rngIng As Range, c As Range, f As Range

Set ws = ThisWorkbook.Worksheets(1)
' Define range with data in column "C"
Set rngIng = ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).row)

j = 2
For Each c In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).row).Cells 'loop Col A
Set f = rngIng.Find(What:=c.Value, LookIn:=xlFormulas, LookAt:=xlWhole)       'search col C
If Not f Is Nothing Then                                                      'any match?
ws.Cells(j, "J").Value = f.EntireRow.Columns("B").Value
j = j + 1
End If
Next

End Sub