结果 放在D、E列:
Sub ss()
Dim i, j, r1, r2, arr, brr
r1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(3).Row
arr = Sheets("Sheet1").Range("A1:B" & r1)
r2 = Sheets("行政区划 ").Cells(Rows.Count, 1).End(3).Row
brr = Sheets("行政区划 ").Range("A2:C" & r2)
For i = 1 To UBound(arr)
If InStr(arr(i, 1), "办事处") Then arr(i, 1) = Replace(arr(i, 1), "办事处", "街道")
Next
For j = 1 To UBound(brr)
If InStr(brr(j, 2), "社区") Then
brr(j, 3) = Replace(brr(j, 2), "社区", "")
ElseIf InStr(brr(j, 2), "村") Then
brr(j, 3) = Replace(brr(j, 2), "村", "")
Else
brr(j, 3) = brr(j, 2)
End If
Next
For i = 1 To UBound(arr)
For j = 1 To UBound(brr)
If arr(i, 1) = brr(j, 1) And InStr(arr(i, 2), brr(j, 3)) Then
arr(i, 2) = brr(j, 2)
Exit For
End If
Next
Next
Sheets("Sheet1").Range("D1:E" & r1) = arr
End Sub |