CODEWITHSUNDEEP
vbaexcel
Sam Basso
Sam Basso

Reputation: 127

Countifs statement compiles but fails with runtime error 438

I have a macro that builds charts on one sheet from data in other sheets in the same book. Currently, it compiles but stops on a countifs statement that looks at dates on one of the other sheets.

The offending line is:

thisBook.rSheet.Range(i, "T") = Application.WorksheetFunction.CountIfs( _
thisBook.sSheet.Cells("K2", "K" & sSheet.Cells(Rows.Count, "K").End(xlUp).Row), "APPROVED", _
thisBook.sSheet.Cells("M2", "M" & sSheet.Cells(Rows.Count, "M").End(xlUp).Row), ">=" & (rSheet.Range(i, "S").Value2 - 6), _
thisBook.sSheet.Cells("M2", "M" & sSheet.Cells(Rows.Count, "M").End(xlUp).Row), "<=" & (rSheet.Range(i, "S").Value2))

The statement is intended to count rows on another sheet where the text in column K equals "APPROVED" and has a date in column M within a specific week . i loops through the 8 rows of this table. Column S is the date that week ends.

Everything has been fully referenced to avoid

runtime error 1004

thisBook is the workbook, rSheet is the sheet with the charts, sSheet is the sheet with the table to evaluate.

Which object doesn't support the property or method here?

Upvotes: 0

Views: 138

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Try this, slightly tweaked to make it easier (I hope) to read/follow:

Dim outputRng As Range
Dim rSheetVal as Date 
Set outputRng = rsheet.Range(i, "T")
rSheetVal = rsheet.Cells(i, "S").Value2   ' OR, rsheet.Range("S"&i).Value2

With thisBook.sSheet
    outputRng = Application.WorksheetFunction.CountIfs( _
                .Range("K2", "K" & .Cells(Rows.Count, "K").End(xlUp).Row), "APPROVED", _
                .Range("M2", "M" & .Cells(Rows.Count, "M").End(xlUp).Row), ">=" & (rSheetVal-6), _
                .Range("M2", "M" & .Cells(Rows.Count, "M").End(xlUp).Row), "<=" & (rSheetVal))
End With

Assuming the thisBook and rsheet are set like:

Set thisBook = ActiveWorkbook
Set rSheet = thisBook.Sheets("r Sheet")

Upvotes: 1

Related Questions