Offset error in one, but not another workbook

    Hi,
    Any idea why the following code would error out in line 2 on one workbook but not another? I copied the one line, then the two and then the entire code from one workbook to another. It works in one, but not in the other. (Error 1004)

    Code
    Dim FirstBlank As Range
Set FirstBlank = Range("A5").End(xlDown).Offset(1, 0)

    Thank you,
    Stefan

    Re: Offset error in one, but not another workbook


    Hi Stefan,


    If you manually select cell A5 in the sheet in which the error occurs, then press Ctrl + Down Arrow, does this take you to the last row in the column? If it does, the error occurs because you're trying to set FirstBlank equal to the row below the last row on the worksheet, which doesn't exist.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Offset error in one, but not another workbook


    Hi Batman,
    I will give that one a try when i get back - in about an hour.
    One thing i just noticed is, that i get an error, even if i select a cell not in column A. Something's funky.
    I appreciate the time you took to give me the other hint, i'll check on that and post back.
    Stefan

    Re: Offset error in one, but not another workbook


    Try putting a test in the code to see what the situation is after you've done the End Down part:

    Code
    Set FirstBlank = Range("A5").End(xlDown)
MsgBox "Last row is " & FirstBlank.Row
If FirstBlank.Row < 65536 Then
    Set FirstBlank = FirstBlank.Offset(1, 0)
End If

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Offset error in one, but not another workbook


    Generally, I prefere to work from the last row upwards

    Code
    Set FirstBlank = Cells(Rows.Count.End(xlUp).Offset(1,0)

    Re: Offset error in one, but not another workbook


    So would I, but due to the absence of actual data I don't know whether the first blank row is actually the last row used + 1. Using .End(xlDown) has problems if you start from the first item and there is only one item in the list, as it doesn't find the last item in that list, but the next non-blank cell or the bottom of the column.


    Stefan, perhaps you could let us know whether you are able to work upwards from the bottom of the sheet instead of downwards?

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Offset error in one, but not another workbook


    Hi,
    Thank you for your continued work with me on this.
    I am uncertain of the differences between xlDown and xlUp and could not find much that makes me any brighter on this either. Anything that makes me a grain smarter is appreciated.


    I suppose it would help to clarify what i am now looking to achive in order to not waste your time and to allow you to help me further.


    The spreadsheet i am working with (there are actually two, which have the same general set-up, for different data though) has data in columns A:P and the other A:M. Although most of the rows will have data in all columns, there are rows where any given number of cells, in any combination, may be blank.


    The first request was to force the user to use that last empty row. Now the request changed to remind the user to use the last row and not just any empty row.


    I would like to find the next available empty row and if the user is attempting to enter data in any row below that, have a msgbox telling the user that s/he should enter date in the last empty row, not the current selected one.


    Something like
    if current active row <> last empty row then msgbox


    Stefan

    Re: Offset error in one, but not another workbook


    If you use xlDown then the last cell will be the first empty cell, not necessarily the last cell. If you start from the last row of the sheet and use xlUp as i suggested, then you will lways find the last row of the data.

    Re: Offset error in one, but not another workbook


    Hi Roy,

    Quote
    Code
    Set FirstBlank = Cells(Rows.Count,End(xlUp).Offset(1,0)


    What do i need to change to not error out at "End"?
    Stefan

    Re: Offset error in one, but not another workbook


    There's a typo, you have a comma instead of a full stop.

    Code
    Set FirstBlank = Cells(Rows.Count.End(xlUp).Offset(1,0)

    Re: Offset error in one, but not another workbook


    Hi Roy,

    Quote

    There's a typo, you have a comma instead of a full stop.

    Indeed, you are right, however, the code you gave is still erroring out. The comma came in when i fiddled with it. There is still something missing/wrong with it. Either a "(" missing or a ")" to much or else! A total of three "(" open and only two ")" close.

    Code
    Set FirstBlank = Cells(Rows.Count.End(xlUp).Offset(1,0)


    Stefan

    Re: Offset error in one, but not another workbook


    Hi Roy,
    i fear you're gonne hate me, but

    Quote

    You need a bracket ) at the end

    when i add the bracket, and work with this,

    Code
    Set firstblank = Cells(Rows.Count.End(xlUp).Offset(1, 0))

    it errors out on ".Count" with "compile error" - "Incorrect qualifier". I think i give up, and then again, now i want to know it.
    Stefan

    Re: Offset error in one, but not another workbook


    Sorry, we ned he Column stating

    Code
    Set firstblank = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

    Re: Offset error in one, but not another workbook


    Hi Roy,
    Thank you, this works. So i suppose you have to have the column reference (1 for A).
    Stefan

    Re: Offset error in one, but not another workbook


    That's right - if you are using R1C! references to refer to A! you would use

    Code
    Cells(1,10 'R1C1
'or
Range("a1")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login