How to fix VBA "Compile Error: Object Required" error with setting dim?

Question

I'm sure it's basic misunderstanding with setting variables...

I have a task to make macro (already have a regular Excel function to solve problem and it works)

=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)

But when debugging macro catch "Object required" for:

Set strbranch = report.Cells(4, 3).Value

I'm sure same error will be actual for:

Set ddate = report.Cells(2, 3).Value

Here is the function so far (updated after all suggestions, now it work and count with parametres correctly) Hurrah!:

    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

And Thanks alot for help!

You must _set_ `lib` to something before setting the ranges. — Gustav, Oct 23 '21 at 10:29
`Dim result As Integer` should be `Dim result1 As Integer, result2 as Integer` or `Long` would be better. — CDP1802, Oct 23 '21 at 12:01
Try `Dim ddate as Double` and `ddate = report.Cells(2, 3).Value2` — CDP1802, Oct 23 '21 at 14:25

score 1 · Accepted Answer · answered Oct 23 '21 at 11:00

1

You are not able to assign string (or another basic type) with Set key word. You need just assign the value.

See this links for more info

Object required (Error 424)

Object doesn't support this property or method (Error 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

answered Oct 23 '21 at 11:00

Michael Navara

1,111
2
8
13

Wow! Thank you! I feel so stupid XD, now ill go work with type mismatch XD – Peter Oct 23 '21 at 11:18
hm, may you suggest with using logic in formula (i fix macro and its work), but didn't count one fragment: `result2 = WorksheetFunction.CountIf(rngdate, "<" & ddate)` With watches in brackemode after executing `ddate Value = 01.01.2020 Type = Variant/Date`, and `result2 Value = 0 and Type Integer`. – Peter Oct 23 '21 at 12:56

How to fix VBA "Compile Error: Object Required" error with setting dim?

1 Answers1