請問各位大大,我在run一個VBA SUB在.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)偵錯有資料型態不符合,如下圖所示,請協助更正,謝謝!
Sub wsj()
Application.EnableEvents = False
Application.Interactive = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Dim i As Long, j As Long, time0#
time0 = Timer
Dim ary, x, y As Variant
With Worksheets("工作表1")
For i = 1 To 50
.Range("h2") = i
For j = 40 To 100 - i
.Range("l2") = j
x = .Range("m14")
If x < 61 Then
y = .Range("ba14")
If y > 19 Then
If IsArray(ary) Then
ReDim Preserve ary(2, UBound(ary, 2) + 1) As Variant
Else
ReDim ary(2, 0) As Variant
End If
ary(0, UBound(ary, 2)) = i
ary(1, UBound(ary, 2)) = j
ary(2, UBound(ary, 2)) = x
End If
End If
Next j
If (i Mod 10) = 0 Then
Debug.Print ("期數 i = " & i)
End If
Next i
.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End With
Application.EnableEvents = True
Application.Interactive = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
MsgBox "執行時間" & Application.Round((Timer - time0) / 3600, 0) & "分"
End Sub
原因是ary還未被定義為"排列"(即是說未有執行過ReDim這兩行程式),到後面執行UBound(ary, 2)時,便會產生"資料型態不符合"問題。
要避開這個問題,可以加入以下修改:
If VarType(ary) = (vbVariant Or vbArray) Then '確定ary是Variant裏的排列
.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End If