本帖最后由 YZC51 于 2020-2-12 20:02 编辑
学习老师的强大公式。谢谢老师!
Sub test()
Application.ScreenUpdating = False '关闭屏幕刷新
With Sheet2
myr = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To myr
.Cells(i, 3) = "=LOOKUP(1,0/FREQUENCY(-9^9,-MMULT((1-ISERR(FIND(MID($B" & i & ",COLUMN($A:$Z),1),PHONETIC(OFFSET(全国省市区!$A$1:$C$1,ROW($1:$2866),)))))*(30-COLUMN($A:$Z)),ROW(1:26)^0)),全国省市区!A$2:A$3)"
.Cells(i, 4) = "=LOOKUP(1,0/FREQUENCY(-9^9,-MMULT((1-ISERR(FIND(MID($B" & i & ",COLUMN($A:$Z),1),PHONETIC(OFFSET(全国省市区!$A$1:$C$1,ROW($1:$2866),)))))*(30-COLUMN($A:$Z)),ROW(1:26)^0)),全国省市区!B$2:B$3)"
.Cells(i, 5) = "=LOOKUP(1,0/FREQUENCY(-9^9,-MMULT((1-ISERR(FIND(MID($B" & i & ",COLUMN($A:$Z),1),PHONETIC(OFFSET(全国省市区!$A$1:$C$1,ROW($1:$2866),)))))*(30-COLUMN($A:$Z)),ROW(1:26)^0)),全国省市区!C$2:C$3)"
.Range("C" & i & ":E" & i) = .Range("C" & i & ":E" & i).Value
Next
End With
Application.ScreenUpdating = True
End Sub
|