Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Western part of Georgia, USA

    Unanswered: question on script

    Can someone help me understand why I get error 1004 from this script?
    I am trying to find the first empty cell in colum A and select it so that I can paste additional data. It fails at the Range(KSB1_Hdr).Select

    Dim KSB1_lastHdr As Integer
    KSB1_lastHdr = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count + 1
    Dim KSB1_Hdr As String
    KSB1_Hdr = "A" & KSB1_lastHdr & ""

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Lee,

    Selecting objects can be very buggy in Excel. My first guess would be that your code is in a worksheet's class module (not the "KSB1" worksheet) which means that your unqualified range property will refer to the range on that worksheet rather than the "KSB1" worksheet. The range object is by design strictly 2 dimensional in the Excel Object Model, so if the named range "KSB1_Hdr" is on the KSB1 worksheet, but your range property is implicitly qualified to a different worksheet, you'll get an error.

    If I've successfully identified the problem there are 3 workarounds:

    1. You can qualify your range property:
    2. Leave the code the same but move your subroutine to a standard code module so the unqualified range property will reference the activesheet.

    3. Best option!
    The bottom line is that 99% of the time we don't need to select ranges - this normally just slows your code down and makes it prone to errors unless you code it very carefully. What do you intend to do with the range, we should be able to show you a better way of doing this?

    Hope that helps....

  3. #3
    Join Date
    Feb 2004
    I think it is likely you are exceeding the limit of the "Integer" Data Type by the number of rows. So to start with you should Type "KBS1_hdr" varible as a Long number vs the integer.

    Now you can do something like this to work better:
    Dim nlastHdr As Long
    ' Set the number of the first blank row
    nlastHdr = Worksheets("KSB1").UsedRange.Rows.Count + 1
    ' Use the 'Cells' Method with the row/column value
    Worksheets("KSB1").Cells(1, nlastHdr).Select


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts