未完成 VBA 关于 index() match() 查到表里空值的处理

用if  ="' , "' ,原来公式

这样处理就行

优化下面代码

    Dim dict2 As Object
    Set dict2 = CreateObject("scripting.dictionary")

    Dim arr7()
    Dim arr8()
    Dim arr9()
    
    
    h0 = Application.Match("skillNum", Worksheets("PetExchange").Range("1:1"), 0)
    h1 = Application.Match(1, Worksheets("PetExchange").Range("2:2"), 0)
    h2 = Application.Match(2, Worksheets("PetExchange").Range("2:2"), 0)
    h3 = Application.Match(3, Worksheets("PetExchange").Range("2:2"), 0)
    h4 = Application.Match(4, Worksheets("PetExchange").Range("2:2"), 0)
    h5 = Application.Match(5, Worksheets("PetExchange").Range("2:2"), 0)
    h6 = Application.Match(6, Worksheets("PetExchange").Range("2:2"), 0)
    h7 = Application.Match(7, Worksheets("PetExchange").Range("2:2"), 0)
    h8 = Application.Match(8, Worksheets("PetExchange").Range("2:2"), 0)
    h9 = Application.Match(9, Worksheets("PetExchange").Range("2:2"), 0)
    h10 = Application.Match(10, Worksheets("PetExchange").Range("2:2"), 0)
    h11 = Application.Match(11, Worksheets("PetExchange").Range("2:2"), 0)
    h12 = Application.Match(12, Worksheets("PetExchange").Range("2:2"), 0)
    h13 = Application.Match(13, Worksheets("PetExchange").Range("2:2"), 0)

    p01 = Application.index(Worksheets("PetExchange").Columns(h1), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p02 = Application.index(Worksheets("PetExchange").Columns(h2), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p03 = Application.index(Worksheets("PetExchange").Columns(h3), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p04 = Application.index(Worksheets("PetExchange").Columns(h4), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p05 = Application.index(Worksheets("PetExchange").Columns(h5), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p06 = Application.index(Worksheets("PetExchange").Columns(h6), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p07 = Application.index(Worksheets("PetExchange").Columns(h7), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p08 = Application.index(Worksheets("PetExchange").Columns(h8), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p09 = Application.index(Worksheets("PetExchange").Columns(h9), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p010 = Application.index(Worksheets("PetExchange").Columns(h10), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p011 = Application.index(Worksheets("PetExchange").Columns(h11), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p012 = Application.index(Worksheets("PetExchange").Columns(h12), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p013 = Application.index(Worksheets("PetExchange").Columns(h13), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
 
 
 
    p01 = Application.index(Worksheets("PetExchange").Columns(h1), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p02 = Application.index(Worksheets("PetExchange").Columns(h2), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p03 = Application.index(Worksheets("PetExchange").Columns(h3), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p04 = Application.index(Worksheets("PetExchange").Columns(h4), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p05 = Application.index(Worksheets("PetExchange").Columns(h5), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p06 = Application.index(Worksheets("PetExchange").Columns(h6), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p07 = Application.index(Worksheets("PetExchange").Columns(h7), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p08 = Application.index(Worksheets("PetExchange").Columns(h8), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p09 = Application.index(Worksheets("PetExchange").Columns(h9), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p010 = Application.index(Worksheets("PetExchange").Columns(h10), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p011 = Application.index(Worksheets("PetExchange").Columns(h11), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p012 = Application.index(Worksheets("PetExchange").Columns(h12), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
    p013 = Application.index(Worksheets("PetExchange").Columns(h13), Application.Match(count, Worksheets("PetExchange").Columns(h0), 0))
 

    
 
 
'           If Len(p01) = 0 Then   'p07 = ""这个不行 IsNull(p01) 也不行？
'              p01 = 0
'           End If
'
'           If Len(p02) = 0 Then
'              p02 = 0
'           End If
'
'           If Len(p03) = 0 Then
'              p03 = 0
'           End If
'
'           If Len(p04) = 0 Then
'              p04 = 0
'           End If
'
'           If Len(p05) = 0 Then
'              p05 = 0
'           End If
'
'           If Len(p06) = 0 Then
'              p06 = 0
'           End If
'
'           If Len(p07) = 0 Then
'              p07 = 0
'           End If
'
'           If Len(p08) = 0 Then
'              p08 = 0
'           End If
'
'           If Len(p09) = 0 Then
'              p09 = 0
'           End If
'
'           If Len(p010) = 0 Then
'              p010 = 0
'           End If
'
'              If Len(p011) = 0 Then
'              p011 = 0
'           End If
'
'           If Len(p012) = 0 Then
'              p012 = 0
'           End If
'
'           If Len(p013) = 0 Then
'              p013 = 0
'           End If
            
 
 
            Debug.Print "p01=" & p01
           Debug.Print "p02=" & p02
           Debug.Print "p03=" & p03
           Debug.Print "p04=" & p04
           Debug.Print "p05=" & p05
           Debug.Print "p06=" & p06
           Debug.Print "p07=" & p07
           Debug.Print "p08=" & p08
           Debug.Print "p09=" & p09
           Debug.Print "p010=" & p010
           Debug.Print "p011=" & p011
           Debug.Print "p012=" & p012
           Debug.Print "p013=" & p013
 
 
    On Error Resume Next
           Debug.Print "Int(p01)=" & Int(p01)
           Debug.Print "Int(p02)=" & Int(p02)
           Debug.Print "Int(p03)=" & Int(p03)
           Debug.Print "Int(p04)=" & Int(p04)
           Debug.Print "Int(p05)=" & Int(p05)
           Debug.Print "Int(p06)=" & Int(p06)
           Debug.Print "Int(p07)=" & Int(p07)
           Debug.Print "Int(p08)=" & Int(p08)
           Debug.Print "Int(p09)=" & Int(p09)
           Debug.Print "Int(p10)=" & Int(p10)
           Debug.Print "Int(p11)=" & Int(p11)
           Debug.Print "Int(p12)=" & Int(p12)
           Debug.Print "Int(p13)=" & Int(p13)
 
 
 
 
 
 
    '先随个数
    On Error Resume Next
    Randomize
    
    
    
'    p0 = p01 * 1 + p02 * 1 + p03 * 1 + p04 * 1 + p05 * 1 + p06 * 1 + p07 * 1 + p08 * 1 + p09 * 1 + p10 * 1 + p11 * 1 + p12 * 1 + p13 * 1
    p0 = Int(p01) + Int(p02) + Int(p03) + Int(p04) + Int(p05) + Int(p06) + Int(p07) + Int(p08) + Int(p09) + Int(p10) + Int(p1) + Int(p12) + Int(p13)
    
    p1 = Int(1 + (p0) * Rnd)
    Debug.Print "p0=" & p0
    Debug.Print "p1=" & p1

如果index() 返回为空

现在试过了，用int() 处理也不好使，int("") 直接就报错

int()不能处理空

同样  p01*1 或者 p01+0 也只对数值型字符串有用

所以现在我的解决办法只能用 len()判断为空，if处理为0 就是显得很啰嗦

Sub tt1()


   On Error Resume Next
    h0 = Application.Match("order", Worksheets("petbag").Range("1:1"), 0)
    h1 = Application.Match("技能1", Worksheets("petbag").Range("1:1"), 0)
    h1 = Application.Match("技能2", Worksheets("petbag").Range("1:1"), 0)

    p01 = Application.index(Worksheets("petbag").Columns(h1), Application.Match(3, Worksheets("petbag").Columns(h0), 0))
    p02 = Application.index(Worksheets("petbag").Columns(h1), Application.Match(99, Worksheets("petbag").Columns(h0), 0))

    Debug.Print " p01= " & p01
    Debug.Print " p02="
    Debug.Print " p02= " & p02


    Debug.Print "p02 * 1=" & p02 * 1
    Debug.Print "Int(p02)=" & Int(p02)

    p0 = p01 * 1 + p02 * 1
    Debug.Print "p0= " & p0

    p0 = Int(p01) + Int(p02)
    Debug.Print "p0= " & p0
    
    


'           If Len(p01) = 0 Then   'p07 = ""这个不行 IsNull(p01) 也不行？
'              p01 = 0
'           End If
'
           

    
           
           
End Sub