求助数组问题

canghai623

各位大神，为什么从这里开始 数组值为空 赋值就提示类型错误,有什么好的解决办法？谢谢


Sub JJ()

Dim arr(), brr(), iRows As Integer, i As Integer, iocv As Double, iimp As Integer, iccv As Double, iocv1 As String, iimp1 As String, iccv1 As String
Dim K As Integer, M As Integer, m1 As Integer, N As Integer, n1 As Integer
iRows = Worksheets("111").Range("a" & Rows.Count).End(xlUp).Row - 4


arr = Worksheets("111").Range("a5:d" & iRows + 4).Value

iocv = Worksheets("111").Range("g3").Value
iocv1 = Worksheets("111").Range("g2").Value
iimp = Worksheets("111").Range("h3").Value
iimp1 = Worksheets("111").Range("h2").Value
iccv = Worksheets("111").Range("i3").Value
iccv1 = Worksheets("111").Range("i2").Value


M = UBound(arr, 1)
N = UBound(arr, 2)
ReDim brr(1 To M, 1 To N)
K = 0
For i = 1 To M

    If Evaluate(arr(i, 2) & iocv1 & iocv) Then
    K = K + 1
        For j = 1 To N

            brr(K, j) = arr(i, j)
        Next j
    End If
Next i

m1 = UBound(brr, 1)
n1 = UBound(brr, 2)
ReDim arr(1 To m1, 1 To n1)
For i = 1 To m1
        For j = 1 To n1
            arr(i, j) = brr(i, j)
        Next j
Next i




M = UBound(arr, 1)
N = UBound(arr, 2)
ReDim brr(1 To M, 1 To N)
K = 0
For i = 1 To M
    If arr(i, 1) = "" Then Exit For   '为什么从这里开始 数组值为空 赋值就提示类型错误,有什么好的解决办法
    If Evaluate(arr(i, 3) & iimp1 & iimp) Then
    K = K + 1
        For j = 1 To N
            brr(K, j) = arr(i, j)
        Next j
    End If
Next i

m1 = UBound(brr, 1)
n1 = UBound(brr, 2)
ReDim arr(1 To m1, 1 To n1)
For i = 1 To m1
        If brr(i, 1) = "" Then Exit For
        For j = 1 To n1
            arr(i, j) = brr(i, j)
        Next j
Next i





M = UBound(arr, 1)
N = UBound(arr, 2)
ReDim brr(1 To M, 1 To N)
K = 0
For i = 1 To M
    If arr(i, 1) = "" Then Exit For
    If Evaluate(arr(i, 4) & iccv1 & iccv) Then
    K = K + 1
        For j = 1 To N
            brr(K, j) = arr(i, j)
        Next j
    End If
Next i

m1 = UBound(brr, 1)
n1 = UBound(brr, 2)
ReDim arr(1 To m1, 1 To n1)
For i = 1 To m1
        If brr(i, 1) = "" Then Exit For
        For j = 1 To n1
            arr(i, j) = brr(i, j)
        Next j
Next i





M = UBound(arr, 1)
N = UBound(arr, 2)
iRows = Worksheets("111").Range("F" & Rows.Count).End(xlUp).Row
Worksheets("111").Range("F5:I" & iRows).Clear
Worksheets("111").Range("F5").Resize(M, N).Value = arr


iRows = Worksheets("111").Range("F" & Rows.Count).End(xlUp).Row
With Range("F5:I" & iRows).CurrentRegion
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = True
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

With Range("F5:I" & iRows)
  .Borders(xlDiagonalDown).LineStyle = xlNone
   .Borders(xlDiagonalUp).LineStyle = xlNone
   .Borders(xlEdgeLeft).LineStyle = xlNone
  .Borders(xlEdgeTop).LineStyle = xlNone
  .Borders(xlEdgeBottom).LineStyle = xlNone
  .Borders(xlEdgeRight).LineStyle = xlNone
   .Borders(xlInsideVertical).LineStyle = xlNone
   .Borders(xlInsideHorizontal).LineStyle = xlNone
End With

With Range("F5:I" & iRows)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With


End Sub

hfwufanhf2006

我下载你文件可以运行啊，没有任何错误提示，我是2010本版；
虽然能运行，但我检查arr(i,1)的时候，发现它是数字，数字与空格是不能放一起判断的，出错的概率很大；

zgwei050

我也是2010，运行正常

canghai623

hfwufanhf2006 发表于 2020-8-3 20:22
我下载你文件可以运行啊，没有任何错误提示，我是2010本版；
虽然能运行，但我检查arr(i,1)的时候，发现它 ...

因为我加了 If arr(i, 1) = "" Then Exit For 这个判断，但是不知哪里出了问题，你把这个行代码去掉再运行就出错

canghai623

hfwufanhf2006 发表于 2020-8-3 20:22
我下载你文件可以运行啊，没有任何错误提示，我是2010本版；
虽然能运行，但我检查arr(i,1)的时候，发现它 ...

因为我 加了这行代码 If arr(i, 1) = "" Then Exit For  才正常，不知道哪里的问题，去除这个判断就出错。

canghai623

zgwei050 发表于 2020-8-3 20:49
我也是2010，运行正常

因为我 加了这行代码 If arr(i, 1) = "" Then Exit For  才正常，不知道哪里的问题，去除这个判断就出错。

canghai623

补充一下 因为我 加了这行代码 If arr(i, 1) = "" Then Exit For  才正常，猜到可能是空格的原因，但是不知道哪里的问题，去除这个判断就出错，请各位帮忙看看什么原因，谢谢

canghai623

是不是 空值不能在 数组间相互赋值？  brr(K, j) = arr(i, j)

金樽空对月

你应该了解一下Evaluate这个函数的具体用法。