数据字典跳过空行赋值问题

vleexhya

Sub A()
Dim arr, d, x, i&, j&, k&, myr&, myr_1&, p_n$, i_c1$, i_c2$, i_c3$, i_c4$, i_c11$, i_c21$, i_c31$, i_c41$
Sheets("2018").Select
Range("A1").Select
Set d = CreateObject("scripting.dictionary")
arr = [A1].CurrentRegion
myr = [c65536].End(xlUp).Row
i = 1
While i > myr
If arr(i, 2) <> "" Then
    i_c1 = WorksheetFunction.Substitute(arr(i, 3), "*", "~*", 1)
    i_c2 = WorksheetFunction.Substitute(arr(i, 4), "*", "~*", 1)
    i_c3 = WorksheetFunction.Substitute(arr(i, 6), "*", "~*", 1)
    i_c4 = WorksheetFunction.Substitute(arr(i, 7), "*", "~*", 1)
    x = i_c1 & "|" & i_c2 & "|" & i_c3 & "|" & i_c4
    d(x) = arr(i, 2)
    i = i + 1
End If
Wend

For k = 2 To myr
    i_c11 = WorksheetFunction.Substitute(arr(k, 3), "*", "~*", 1)
    i_c21 = WorksheetFunction.Substitute(arr(k, 4), "*", "~*", 1)
    i_c31 = WorksheetFunction.Substitute(arr(k, 6), "*", "~*", 1)
    i_c41 = WorksheetFunction.Substitute(arr(k, 7), "*", "~*", 1)
    x = i_c11 & "|" & i_c21 & "|" & i_c31 & "|" & i_c41
If d.exists(x) Then
    Cells(k, 2) = d(x)
    Else
    End If
    Next
End Sub

vleexhya

逻辑：如果C、D、E、F、G列的结合在上面有，自动将其对应的B列的值 给当前列 。
多谢！

蓝桥玄霜

1. Sub A()
   
2. Dim arr, d, x, i&, j&, k&, myr&, myr_1&, p_n$, i_c1$, i_c2$, i_c3$, i_c4$, i_c11$, i_c21$, i_c31$, i_c41$
   
3. Sheets("2018").Select
   
4. Range("A1").Select
   
5. Set d = CreateObject("scripting.dictionary")
   
6. arr = [A1].CurrentRegion
   
7. myr = [c65536].End(xlUp).Row
   
8. For k = 2 To myr
   
9.     i_c11 = WorksheetFunction.Substitute(arr(k, 3), "*", "~*", 1)
   
10.     i_c21 = WorksheetFunction.Substitute(arr(k, 4), "*", "~*", 1)
    
11.     i_c31 = WorksheetFunction.Substitute(arr(k, 6), "*", "~*", 1)
    
12.     i_c41 = WorksheetFunction.Substitute(arr(k, 7), "*", "~*", 1)
    
13.     x = i_c11 & "|" & i_c21 & "|" & i_c31 & "|" & i_c41
    
14. If d.exists(x) Then
    
15.     Cells(k, 2) = d(x)
    
16. Else
    
17.     d(x) = arr(k, 2)
    
18. End If
    
19. Next
    
20. End Sub
    

复制代码

vleexhya

蓝桥玄霜 发表于 2018-9-8 14:33

哇，得到了蓝版的回复 ，数据字典就是看蓝版的帖子 。多谢！