4

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
Community
  • 1
  • 1
Matt Ridge
  • 3,633
  • 16
  • 45
  • 63
  • 6
    `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 Sep 05 '12 at 19:48

1 Answers1

7

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    +1 you answered. I commented. Both of us said the same thing! – Scott Holtzman Sep 05 '12 at 19:50
  • 2
    +1 if you're going to use `ws` as the prefix for `Worksheet` variables then use something different - e.g. `wb` - as the prefix for `Workbook` variables – barrowc Sep 05 '12 at 21:41
  • I was thinking about doing that. I just wanted to barrel though this to get it working before tweaking small things... but you are right. – Matt Ridge Sep 06 '12 at 11:36
  • 1
    @MattRidge - in reference to barrowc's comment, its a good practice to label your variables wisely from the start, as it helps to understand what they are as you are using them in your code. There is a convention out there somewhere, whose name I forget, but perhaps someone else can chime on it. It's basically goes like `Dim strVar as String` `Dim intVar as Integer` `Dim lngVar as Long` That way you know what you are working with. Spending a bit more to do this upfront and develop the discipline will save you many pains later on! – Scott Holtzman Sep 06 '12 at 13:55
  • 1
    @ScottHoltzman thanks, I am self taught so I'm missing a lot of the skills that people here take for granted. – Matt Ridge Sep 06 '12 at 14:58
  • @MattRidge - I am completely self-taught as well and I developed my understanding just like you are... tons of online help! That is why I wrote what I did, to help you along your path. What good is to catch a fish for a man, when you can teach him how to catch is own? – Scott Holtzman Sep 06 '12 at 15:24