我正在尝试比较两个名称列表,并在两个列表中找到名称。但是,列表的顺序不相同,长度不同。
我尝试了一个代码将第一个列表(" A1"(的名字与另一个列表(" b1"(中的第一个名称进行比较,然后将其与第二个(" B3"(进行比较,直到它匹配。如果匹配,则在第三列上写"匹配",或者如果没有匹配项
Sub CompareTest()
Dim iComp As Integer, i As Integer, j As Integer
Dim str1 As string, str2 As string
For i = 1 to 20
str1 = ("A" & i)
For j = 1 to 20
str2 = ("B" & j)
iComp = StrComp(str1, str2, vbTextCompare)
Select Case iComp
Case 0
Range ("C" & i) = "Match"
Case 1
Range ("C" & i) = "Match"
End Select
If Range ("C" & i) = "Match" Then Exit For
Next j
Next i
End Sub
现在,即使有匹配项,代码也会在每个单元格中写入"不是匹配",而我不确定什么不起作用。
在代码中,您正在比较字符串"An"
和"Bn"
,而不是地址的内容。但是,即使您纠正了这一点,如果str1
等于或大于 str2
,您仍在编写"匹配"。可能不是您想要的。
您可能可以做类似的事情:
C1: =IF(COUNTIF(B:B,A1),"Match","Not a Match")
或,在代码
中Option Explicit
Sub matcher()
Dim WS As Worksheet
Dim C As Range, rSearch As Range, rLookup As Range
Set WS = Worksheets("sheet2")
With WS
Set rLookup = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set rSearch = .Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp))
.Columns(3).Clear
End With
For Each C In rLookup
If rSearch.Find(what:=C, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then
C.Offset(0, 2) = "No Match"
Else
C.Offset(0, 2) = "Match"
End If
Next C
End Sub