[已解决]根据订单号查找帐号

Frankhao123

订单号在表1备注栏，没什么规律。希望能用代码实现。

最佳答案

月排行榜 / 总排行榜

dsmch

2016-3-22 06:11

1. Sub Macro3()
   
2. Dim arr, brr, i&, j&
   
3. arr = Sheet1.UsedRange
   
4. Sheet2.Activate
   
5. brr = Range("a1").CurrentRegion
   
6. [b2:b20000] = ""
   
7. For i = 2 To UBound(brr)
   
8.     For j = 2 To UBound(arr)
   
9.         If InStr(arr(j, 20), brr(i, 1)) Then brr(i, 2) = arr(j, 10)
   
10.     Next
    
11.     If brr(i, 2) = "" Then brr(i, 2) = "查不到"
    
12. Next
    
13. Range("b1").Resize(UBound(brr)) = Application.Index(brr, 0, 2)
    
14. End Sub

复制代码

跳转到最佳答案楼层

dsmch

1. Sub Macro3()
   
2. Dim arr, brr, i&, j&
   
3. arr = Sheet1.UsedRange
   
4. Sheet2.Activate
   
5. brr = Range("a1").CurrentRegion
   
6. [b2:b20000] = ""
   
7. For i = 2 To UBound(brr)
   
8.     For j = 2 To UBound(arr)
   
9.         If InStr(arr(j, 20), brr(i, 1)) Then brr(i, 2) = arr(j, 10)
   
10.     Next
    
11.     If brr(i, 2) = "" Then brr(i, 2) = "查不到"
    
12. Next
    
13. Range("b1").Resize(UBound(brr)) = Application.Index(brr, 0, 2)
    
14. End Sub

复制代码

Frankhao123

dsmch 发表于 2016-3-22 06:11

谢谢