hi,
Below are two examples of how i am using countifs in excel vba.
THe first one works......no issues:
junk1 = Application.WorksheetFunction.CountIfs( _
ws_ecrfs.Range(alphacol(ecrf_L1Backlog_col) & 1 & ":" & _
alphacol(ecrf_L1Backlog_col) & lst_row_wsecrfs), "Yes", _
ws_ecrfs.Range(alphacol(ecrf_DaysOutstanding_col) & 1 & ":" & _
alphacol(ecrf_DaysOutstanding_col) & lst_row_wsecrfs), ">" & PageEntryTAT)
However, for some reason the following produces a method range of object worksheet failed error and i can not figure out why
junk4 = Application.WorksheetFunction.CountIfs( _
ws_queries.Range(alphacol(queries_qrystatus_col) & 1 & ":" & _
alphacol(queries_qrystatus_col) & lst_row_wsqueries), "Answered", _
ws_queries.Range(alphacol(queries_L1Backlog_col) & 1 & ":" & _
alphacol(queries_L1Backlog_col) & lst_row_wsqueriesd), "Yes")
Note: i did define the worksheets as follows:
Dim ws_ecrfs As Worksheet
Dim ws_queries As Worksheet
'set link to outstandings worksheet
Set ws_queries = Workbooks(fl_output).Worksheets(ShtOut_OutStandingQueries)
'set link to ecrfs worksheet
Set ws_ecrfs = Workbooks(fl_output).Worksheets(ShtOut_OutstandingeCRfs)
Bookmarks