subscript out of range message 3

[PavelGur]

I'm debugging Excel macro that I partialy copied from another macro that works perfectly well. However in the new one I'm getting error 9 "subscript out of range" on statement Sheets("560 Stocks").select. The name matches perfectly to the name of spreadsheet. In my working macro the same statement Sheets("Stocks 2006").Select works perfectly well. I have the same set of references for both spreadsheets.
What can be a problem?
Thanks, Pavel.

 

[SkipVought]

Hi,

The sheet name is NOT the same as the literal in your statement.

Copy the sheet Tab name and PASTE it into your statement.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[xlbo]

ditto that - error 9 - subscript out of range means exactly that - the thing you are trying to reference does not exist.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Are you referening the correct workbook with the sheet object?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[PavelGur]

thanks guy! I was refering to workbook name instead of sheet name.
Pavel.

 

[firefytr]

As a side note, when dealing with multiple worksheets, always explicitly reference the worksheet name. Also, when dealing with multiple workbooks, always reference the workbook name. You do this because if you do not, excel assumes you are dealing with the active workbook and the active sheet. This is common for people who have recorded macros that "no longer work for them."

Here are some examples...

    '// No reference at all, on the activebook/sheet
    Range("A1").Value = "x"
    
    '// Sheet reference, always on activebook
    Sheets("Sheet1").Range("A1").Value = "x"
    
    '// Book reference, always to the right place
    Workbooks("Book1.xls").Sheets("Sheet1").Range("A1").Value = "x"
    
    '// Using sheet variables
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    ws.Range("A1").Value = "x"
    
    '// Using book variables
    Dim wb As Workbook, wks As Worksheet
    Set wb = Workbooks("Book1.xls")
    Set wks = wb.Sheets("Sheet1")
    wks.Range("A1").Value = "x"

HTH

-----------
Regards,
Zack Barresse

 

[fumei]

Whether there are multiples, or not, it is always better to explicitly name or point to things. Be it sheets, or workbooks, or documents, or tables, or paragraphs, or ranges, or controls, or....

X is THAT thing (cell, paragraph, sheet etc.)
vs
X is A thing (cell, paragraph, sheet etc.)

...unless of course you are looping through A bunch of things...



Gerry