0

## excel vba 資料型態不符合問題

``````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
``````

### 1 個回答

0
marlin12
iT邦新手 1 級 ‧ 2019-12-11 18:49:01

``````If VarType(ary) = (vbVariant Or vbArray) Then  '確定ary是Variant裏的排列
.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End If
``````

``````If IsArray(ary) Then
.Range("bd2").Resize(UBound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End If
``````