|
|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 lrqiang 于 2019-5-10 01:21 编辑
请高手帮助优化，数据过多时运行太慢
end_1 = Sheets("数据").Cells(65536, "A").End(xlUp).Row '确定数据的记录数符值给变量 end_1
end_2 = Sheets("路街巷").Cells(65536, "A").End(xlUp).Row '确定数据的记录数符值给变量 end_1
Sheets("数据").Select
Range("B2:H40000").Select
Selection.ClearContents
Application.CutCopyMode = False
For i = 2 To end_1
a1 = Trim(Cells(i, "A").Value)
a2 = Len(Trim(a1))
'''''''''''''''除"广东省广州市番禺区榄核镇"
If Left(a1, 12) = "广东省广州市番禺区榄核镇" Then
a1 = Trim(Mid(a1, 13, 50))
End If
If Left(a1, 9) = "广州市番禺区榄核镇" Then
a1 = Trim(Mid(a1, 10, 50))
End If
If Left(a1, 6) = "番禺区榄核镇" Then
a1 = Trim(Mid(a1, 7, 50))
End If
If Left(a1, 3) = "榄核镇" Then
a1 = Trim(Mid(a1, 4, 50))
End If
B1 = ""
b2 = ""
b3 = ""
b4 = ""
C1 = 0
C2 = 0
C3 = 0
C4 = 0
If a2 > 0 Then
For j = 1 To a2
a3 = Mid(a1, j, 1)
If InStr("0123456789", a3) = 0 And C2 = 0 And C3 = 0 And C4 = 0 Then
B1 = B1 + a3
C1 = 1
End If
If InStr("0123456789", a3) > 0 And C3 = 0 And C1 = 1 And C4 = 0 Then
b2 = b2 + a3
C2 = 1
End If
If InStr("0123456789", a3) = 0 And C4 = 0 And C1 = 1 And C2 = 1 Then
b3 = b3 + a3
C3 = 1
End If
If InStr("0123456789", a3) > 0 And C1 = 1 And C2 = 1 And C3 = 1 Then
b4 = b4 + a3
C4 = 1
End If
Next j
Cells(i, "B").Value = Trim(B1)
Cells(i, "C").Value = b2
Cells(i, "D").Value = Trim(b3)
Cells(i, "E").Value = b4
With Sheets("路街巷").Range("A1").Resize(end_2, 1)
B1 = Trim(B1)
Set C = .Find(B1)
If Not C Is Nothing Then
Sheets("数据").Cells(i, "F").Value = C.Offset(, 1).Value
Sheets("数据").Cells(i, "G").Value = C.Offset(, 2).Value
End If
End With
For k = 1 To end_2
d1 = Trim(Sheets("路街巷").Cells(k, "A").Value)
If a1 Like ("*" + d1 + "*") And Len(Sheets("数据").Cells(i, "G").Value) = 0 Then
Sheets("数据").Cells(i, "F").Value = Sheets("路街巷").Cells(k, "B").Value
Sheets("数据").Cells(i, "G").Value = Sheets("路街巷").Cells(k, "C").Value
End If
Next k
End If
If i > 40 Then
ActiveWindow.SmallScroll Down:=1
End If
Next
Range("B2").Select
End Sub |
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2019-5-11 08:40
楼主