SubScript Out Of Range.

[Sam]

Last week some nice folks here gave me a few tips, and I was able to get
the macro I had been working on to run properly. Thanks!

But today, I ran the same macro on a worksheet that was laid out exactly
like the earlier one, and I got `Subscript Out Of Range.' error. I made no
chages to the code.

Here is the line that program stopped at:

FullName = Worksheets("Sheet1").Cells(I, 4).Value

I thought maybe the error was caused because the macro was not actually
in the workbook I was using. I copied it to that workbook, but same thing
happened.

Anybody have any idea??

Sam

A man who had lately declared
That property ought to be shared,
Thought it going too far
When they called for his car,
And a list of exceptions prepared.

Thomas Thorneley,
From The Penguin
Book Of Limericks

 

[Sam]

Last week some nice folks here gave me a few tips, and I was able to get
the macro I had been working on to run properly. Thanks!

But today, I ran the same macro on a worksheet that was laid out exactly
like the earlier one, and I got `Subscript Out Of Range.' error. I made no
chages to the code.

Here is the line the program stopped at:

FullName = Worksheets("Sheet1").Cells(I, 4).Value

Apparently the syntax has to be:

FullName = Worksheets(1).Cells(I, 4).Value

I have no clue why, but my program runs after I made that change.

 

[Ron de Bruin]

Maybe you sheet name have a space before it like
" Sheet1"

 

[Tom Ogilvy]

msgbox "==>" & Worksheets(1).name & "<==="

would let you know what the actual name is. You might have a space or two
on one end or the other.

 

[pikus]

The line of code you included your coordinates are wrong. You listed i
as (I, 4). Now if you want cell "I4" as it is in Excel, you need t
change what you have to (4, 8). This is because VBA uses X
coordinates. The first number corresponds to the rows in Excel and th
second to the columns. Assuming I guessed correctly, you want th
fourth cell down in the eight column. Anyway, VBA also lets you us
the format ("I4") in some cases, though not always, so I find it easie
to only use the one way that always works all the time. It cuts dow
on the confusion. - Pikus: