這個問題在這里已經有了答案： vba中的dim和set有什么區別 7個回答 昨天關閉。
我確定這是對設定變數的基本誤解......
我有一個任務來制作宏（已經有一個常規的 Excel 函式來解決問題并且它有效）
=COUNTIFS(Test!$G:$G;Лист1!C4;Test!$I:$I;Page1!K2)
COUNTIFS(Test!$G:$G;Page1!C4;Test!$I:$I;Page1!L2)
COUNTIFS(Test!$G:$G;Page1!C4;Test!$I:$I;Page1!M2)
但是在除錯宏時捕獲“需要的物件”：
Set strbranch = report.Cells(4, 3).Value
我確定相同的錯誤將適用于：
Set ddate = report.Cells(2, 3).Value
這是到目前為止的功能（在所有建議之后更新，現在它可以正常作業并正確計算引數）萬歲！：
Sub count_if()
Dim rngstatus As Range
Dim rngbranch As Range
Dim rngdate As Range
Dim report As Worksheet
Dim lib As Worksheet
Dim result1 As Integer
Dim result2 As Integer
Dim strbranch
Dim ddate As Double
Dim val1
Dim val2
Dim val3
Set lib = Worksheets("Test")
Set report = Worksheets("Report")
Set rngstatus = lib.Range("$I:$I")
Set rngbranch = lib.Range("$G:$G")
Set rngdate = lib.Range("$F:$F")
strbranch = report.Cells(4, 3).Value
ddate = report.Cells(2, 3).Value2
Set val1 = "Word1"
Set val2 = "Word2"
Set val3 = "Word3"
result1 = WorksheetFunction.countIFS(rngbranch, strbranch, rngstatus, val1) _
WorksheetFunction.countIFS(rngbranch, strbranch, rngstatus, val2) _
WorksheetFunction.countIFS(rngbranch, strbranch, rngstatus, val3)
result2 = WorksheetFunction.countIFS(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val1) _
WorksheetFunction.countIFS(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val2) _
WorksheetFunction.countIFS(rngdate, "<" & ddate, rngbranch, strbranch, rngstatus, val3)
MsgBox "Actual " & result1 & ", Failed deadline" & result2
report.Cells(6, 3) = result1
report.Cells(7, 3) = result2
End Sub
非常感謝您的幫助！
uj5u.com熱心網友回復：
您無法使用Set關鍵字分配字串（或其他基本型別）。您只需要分配值。
查看此鏈接了解更多資訊
需要物件（錯誤 424）
物件不支持此屬性或方法（錯誤 438）
Sub SetTest()
Dim a
a = "Some string" 'This works
'Set a = "Some string" 'Object required (Error 424)
Dim b
'b = ActiveWorkbook 'Object doesn't support this property or method (Error 438)
Set b = ActiveWorkbook 'This works
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/334640.html
上一篇：如何在VBA中批量切片陣列