Exit For放在ELSE之前。 Sub 导入1() '蓝桥老师对定向导入2进行优化,速度提升2倍 '1,声明变量类型用as Long(简写&),适用数据量大的情况; '2,用数组经过优化,只需要0.34秒。 'Dim Sht1 As Worksheet, Sht2 As Worksheet Dim Arr, Arr1, Arr2, t Dim Myr1&, Myr2&, h&, k& 'Set Sht1 = Sheets("数据源") 'Set Sht2 = Sheets("结果表") 't = Timer Application.ScreenUpdating = False 'Myr1 = Sht1.Range("a65536").End(xlUp).Row 'Myr2 = Sht2.Range("a65536").End(xlUp).Row Myr1 = Sheet3.Range("q65536").End(xlUp).Row '数据源 Myr2 = Sheet3.Range("b65536").End(xlUp).Row '结果表 ' Arr = Sheet3.Range("ad2:ad" & Myr1) ' Arr1 = Sheet3.Range("e2:e" & Myr2) Arr = Sheet3.Range("q2:q" & Myr1).Value '数据源 Arr1 = Sheet3.Range("e2:e" & Myr2).Value '结果表 'ReDim Arr2(1 To Myr2 - 1, 1 To 2) ReDim Arr2(1 To Myr2 - 1, 1 To 1) For h = 1 To UBound(Arr1) For k = 1 To UBound(Arr) If CStr(Arr1(h, 1)) = CStr(Arr(k, 1)) Then Arr2(h, 1) = "行政" Exit For Else Arr2(h, 1) = "公司" End If Next k Next h Sheet3.Range("a2:a" & Myr2) = Arr2 Application.ScreenUpdating = True Sheet3.Activate End Sub |