Excel VBA, error 438 "object doesn't support this property or method

Question

I have this code in which I've been getting help with a bit, but I've run into an issue, or what I think is an issue. The last lookup, I am being told that the object doesn't support this property or method. I know it's probably something easy, but my brain is smoldering. I'd like some help if someone knows the answer of why this is happening.

Thanks.

Option Explicit

Sub Update_Dakota()

    Dim wsDAO As Worksheet              'Dakota OOR
    Dim wsDAD As Worksheet              'Dakota Data
    Dim wsDAR As Worksheet              'Dakota Archive
    Dim wsPOR As Workbook               'New Workbook
    Dim lastrow As Long, fstcell As Long
    Dim strFile As String, NewFileType As String, filename As String

    Set wsDAO = Sheets("Dakota OOR")
    Set wsDAD = Sheets("Dakota Data")
    Set wsDAR = Sheets("Dakota Archive")


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    lastrow = wsDAD.Range("B" & Rows.Count).End(xlUp).Row + 1

    With wsDAD
        .Range("I2").Formula = "=COUNTIFS('Dakota OOR'!$B:$B,$A2,'Dakota OOR'!$D:$D,$C2, 'Dakota OOR'!$G:$G,$F2)"
        .Range("J2").Formula = "=IF(I2,""Same"",""Different"")"
        wsDAD.Range("I2:J2").Copy wsDAD.Range("I3:J" & lastrow)
        wsDAD.Range("I:J").Calculate
    End With


    strFile = Application.GetOpenFilename()
    NewFileType = "Excel Files 2007 (*.xls)"
    Set wsPOR = Application.Workbooks.Open(strFile)
    lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

    wsPOR.Range("A2:G" & lastrow).Select


End Sub

lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1 has no worksheet associated with it. wsPOR is set your workbook. You can only find ranges on worksheets. Also, the next line, wsPOR.Range("A2:G" & lastrow).Select needs to refer to a worksheet, not workbook. — Scott Holtzman

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-09-05T19:46:00

The Error is here

lastrow = wsPOR.Range("A" & Rows.Count).End(xlUp).Row + 1

wsPOR is a workbook and not a worksheet. If you are working with "Sheet1" of that workbook then try this

lastrow = wsPOR.Sheets("Sheet1").Range("A" & _
          wsPOR.Sheets("Sheet1").Rows.Count).End(xlUp).Row + 1

Similarly

wsPOR.Range("A2:G" & lastrow).Select

should be

wsPOR.Sheets("Sheet1").Range("A2:G" & lastrow).Select

Excel VBA, error 438 "object doesn't support this property or method

1 Answers