Coutnifs in Excel VBA is Not Working

I'm having trouble with getting my code to work (see below). The problem is the countifs function in the last section of the code. I feel that I have formatted it right but an error message is thrown up each time it gets to this part of the code. Error message is "Run-time error '438': Object doesn't support this property or method." Any suggestions are appreciated.

Sub manipulate()

Dim wkb1, wkb2 As Workbook
Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("F:\Flat Panel Engineering\09 MIT\Data\Yield-

Sheets("Raw Data - MES Yield").Activate

Dim cell, column As Range
Set column = Range("E1:E416650")
For Each cell In column
    cell.Value = Application.VLookup(cell.Offset(0, 1), _ 
    wkb1.Sheets("PF").Range("A1:B80"), 2, False)
Next cell

Range("A3").End(xlToRight).Offset(0, 1).Select
ActiveCell.Value = WorksheetFunction.CountIfs(wkb2.Range("E:E"), _
    Range("A3"), wkb2.Range("J:J"), Range("B3"), _
    wkb2.Range("K:K"), Range("C2"), wkb2.Range("N:N"), Range("D1"))

wkb2.Close savechanges:=False

End Sub

2 answers

  • answered 2018-03-13 21:55 QHarr

    for only the error described you need to add Application before WorksheetFunction and all the ranges need a worksheet name between the workbook part and the range part. You are currently frequently saying Workbook.Range which is invalid.

    ActiveCell.Value = Application.WorksheetFunction.CountIfs(wkb2.Worksheets("insertworksheetname").Range("E:E"), wkb1.Worksheets("insertworksheetname").Range("A3"), wkb2.Worksheets("insertworksheetname").Range("J:J"), wkb1.Worksheets("insertworksheetname").Range("B3"), _
        wkb2.Worksheets("insertworksheetname").Range("K:K"), wkb1.Worksheets("insertworksheetname").Range("C2"), wkb2.Worksheets("insertworksheetname").Range("N:N"), wkb1.Worksheets("insertworksheetname").Range("D1"))

    You will get away with the ones where you have just put Range as they reference the active sheet but it is always better to explicitly reference the sheet by name.

  • answered 2018-03-13 22:17 chris neilsen

    As already answered, Workbook's don't have Range's, Worksheets do.

    Where you use wkb2.Range("E:E") etc, add a reference to the required sheet


    wkb2.Worksheets("Raw Data - MES Yield").Range("E:E")

    Some side notes

    1. where you use Dim wkb1, wkb2 As Workbook only wkb2 is declared as Workbook (wkb1 will be a Variant). Be explicit for each variable on the line

    2. it's better to avoid activating / selecting.

    3. you can avoid the VLookup loop, which be be a lot faster

    Your code refactored to demonstrate

    Sub manipulate()
        Dim wkb1 As Workbook, wkb2 As Workbook
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set wkb1 = ThisWorkbook
        Set wkb2 = Workbooks.Open _
          ("F:\Flat Panel Engineering\09 MIT\Data\Yield-Symptom_Report_rev05.xlsx")
        Set ws2 = wbk2.Worksheets("Raw Data - MES Yield")
        Dim column As Range
        Set column = ws2.Range("E1:E416650")
        column = Application.VLookup(column.Offset(0, 1), _
          wkb1.Worksheets("PF").Range("A1:B80"), 2, False)
        Set ws1 = wbk1.Worksheets("NameOfWb1Sheet")
        ws1.Range("A3").End(xlToRight).Offset(0, 1).Value = _
          Application.WorksheetFunction.CountIfs( _
          ws2.Range("E:E"), ws1.Range("A3"), _
          ws2.Range("J:J"), ws1.Range("B3"), _
          ws2.Range("K:K"), ws1.Range("C2"), _
          ws2.Range("N:N"), ws1.Range("D1"))
        wkb2.Close savechanges:=False
    End Sub