我已经编写了以下代码,用于比较A列和D列中的两个单元格(字符串),如果找到部分匹配,则将D单元格的值写入相应的B单元格。
Sub CompareAndGuess()
Dim strLen, aux As Integer
Dim max1, max2 As Long
Dim str As String
Range("A1").Select
Selection.End(xlDown).Select
max1 = ActiveCell.Row
Range("D1").Select
Selection.End(xlDown).Select
max2 = ActiveCell.Row
For a = 2 To max1
str = Cells(a, 1)
str = StrConv(str, vbUpperCase)
strLen = Len(str)
aux = strLen
For l = 3 To strLen
For d = 2 To max2
If Cells(d, 4) = Left(str, aux) Then
Cells(a, 2) = Cells(d, 4)
Exit For
ElseIf Cells(d, 4) = Right(str, aux) Then
Cells(a, 2) = Cells(d, 4)
Exit For
End If
Next d
aux = aux - 1
If Cells(a, 2) <> "" Then
Exit For
End If
Next l
Cells(a, 2).Select
Next a
End Sub
能否有人帮我找出问题所在?当我运行代码时,它只能正确猜测50行中的一行,而应该至少匹配40行左右。
请帮忙看看,我真的找不到错误所在。如果您愿意,可以提出另一个解决方案来解决我的问题。
我正在分析的数据样本如下:
存在拼写错误的名称:
Jatiuca
Pajuara
Poco
Santa Luzia
Pajucara
Domingos Acacio
Jaragua
Stella Maris
P Verde
Tabuleiro dos Martin
Gruta Lourdes
Brasilia
Centro Historico
Monumento
Tabuleiro dos Martins
需要在此列表中搜索的拼写错误名称:
JARAGUÁ
TABULEIRO DO MARTINS
CENTRO
BRASÍLIA
CACIMBAS
JATIÚCA
CAITITUS
PAJUÇARA
CANAÃ
PONTA VERDE
CANAFÍSTULA
POÇO
CAPIATÃ
CAVACO
SANTA LÚCIA
InStr
函数,这可能会引起您的兴趣... - Mathieu Guindon