时间戳

首页 > 解决方案 > 按 ROW 对列进行索引并在字符串中查找部分匹配项

excel - 按 ROW 对列进行索引并在字符串中查找部分匹配项

问题描述

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

分解问题：

我有两张要比较的表格 - 一张存在于 sheet1 列 b 中，另一张存在于 sheet2 列 c 中。

for 循环将遍历Sheet1上的 B 列，然后在每一行提取当前字符串 - 然后传递当前字符串然后将 ADJACENT 列返回到 C 列右侧，然后将此值存入 B 列右侧的 ADJACENT 列。

我目前已尝试实现一个 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

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

标签： excelvba

解决方案

像这样的东西应该工作：

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

推荐阅读