按列上的 ROW 编制索引，并在字符串中查找部分匹配项

Question

我在开发包含部分匹配功能的 for 循环时遇到问题。

要分解问题，请执行以下操作：

我有两张要比较的纸 - 一张存在于工作表 1 b 列上，另一张存在于工作表 2 列 c 上。

for 循环将遍历 Sheet1 上的 B 列，然后在每一行提取当前字符串 - 然后传递此当前字符串我尝试了 vlookup并与工作表 2 上的整个列 C 进行比较以找到匹配项：如果有匹配项，它将返回 C 列右侧的 ADJACENT 列，然后将此值存入 B 列右侧的相邻列。

我目前尝试实现一个 for if 语句，该语句循环访问 b 列，如果 B 列的当前字符串等于当前字符串匹配的 C 列的 Vlookup，则返回该值。

Sub JoinGroupOnPN()
Dim PartGroupSheet As Worksheet
Dim OEEPartSheet As Worksheet    
Dim OEERowRange As Long    
Dim OEEColumnRange As Long    
Dim PGRowRange As Long    
Dim PGColumnRange As Long    
Dim OEEArray As Variant    
Dim PGArray As Variant    
Dim i As Long, j As Long
Set PartGroupSheet = ActiveWorkbook.Worksheets("PartGroup")    
Set OEEPartSheet = ActiveWorkbook.Worksheets("OEE Report")    
OEERowRange = OEEPartSheet.Cells(Rows.Count, 1).End(xlUp).Row    
OEEColumnRange = OEEPartSheet.Cells(1,Columns.Count).End(xlToLeft).Row    
PGRowRange = PartGroupSheet.Cells(Columns.Count, 1).End(xlUp).Row    
PGColumnRange = PartGroupSheet.Cells(1,Columns.Count).End(xlToLeft).Row
ReDim OEEArray(OEERowRange, OEEColumnRange)    
ReDim PGArray(PGRowRange, PGColumnRange)    
Dim StringToMatch As String    
Dim MatchingString As String
For i = 2 To OEERowRange
StringToMatch = OEEPartSheet.Cells(i, 1).Text
MatchingString = Application.WorksheetFunction.VLookup(Arg1:=StringToMatch, Arg2:=PartGroupSheet.Range(Cell1:=2, Cell2:=1), Arg3:=2, Arg4:=True)
For j = 2 To PGRowRange
If StringToMatch = MatchingString Then
Debug.Print StringToMatch
End If
Next j
Next i
End Sub

我不断收到一个错误，说范围对象失败，我尝试将其转换为范围类型，但仍然相同的错误。

错误发生在

MatchingString = Application.WorksheetFunction.VLookup(Arg1:=StringToMatch, Arg2:=PartGroupSheet.Range(Cell1:=2, Cell2:=1), Arg3:=2, Arg4:=True)

错误消息是"对象"的方法"范围"_WorkSheet失败

我还不能发布任何图片

表 1 表 2

任何帮助将不胜感激，谢谢！

Answer 1

这样的事情应该可以工作：

Sub JoinGroupOnPN()
Dim PartGroupSheet As Worksheet
Dim v, c As Range, rngSrch As Range
Set PartGroupSheet = ActiveWorkbook.Worksheets("PartGroup")
With ActiveWorkbook.Worksheets("OEE Report")
Set rngSrch = .Range(.Range("B2"), .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each c In rngSrch.Cells
If Len(c.Value) > 0 Then
'do not use WorksheetFunction here
v = Application.VLookup(c.Value, PartGroupSheet.Range("B:C"), 2, False)
c.Offset(0, 1).Value = IIf(IsError(v), "No match", v)
End If
Next c
End Sub