Hi;

i'm trying to capture a value in a range. I have the following codeattched
to the click action on a button in my "Results" sheet:

Dim LastDataRow As Integer

LastDataRow = Worksheets("Data").Range("E1").Value

When the above line is reached, I get a "Run time error '9' Subscript out of
range" error message.

Cell E1 of the worksheet labelled "Data" has the value 9 in it. I tried
displaying the value of it by the line :

MsgBox Worksheets("Data").Range("E1").Value

which was placed just before the problem line. When the MsgBox line
executed, the value was blank in the message box.

I'm at a loss as to what the problem is. Any help is greatly appreciated.

--
JJFJR

Hi,

Yeah just change Worksheets to Sheets. Different Collections so its not
registering what you are trying to do.

Any Problems then give me a shout.

James

Hi JJFJR,

The "Subscript out of range" message indicates a failure to find a worksheet
named 'Data'.

Check that there are no spaces at the beginning or end of the sheet's name.


---
Regards,
Norman



"jjfjr" wrote in message
...
Hi;

i'm trying to capture a value in a range. I have the following codeattched
to the click action on a button in my "Results" sheet:

Dim LastDataRow As Integer

LastDataRow = Worksheets("Data").Range("E1").Value

When the above line is reached, I get a "Run time error '9' Subscript out
of
range" error message.

Cell E1 of the worksheet labelled "Data" has the value 9 in it. I tried
displaying the value of it by the line :

MsgBox Worksheets("Data").Range("E1").Value

which was placed just before the problem line. When the MsgBox line
executed, the value was blank in the message box.

I'm at a loss as to what the problem is. Any help is greatly appreciated.

--
JJFJR

Sorry, just ignore what I wrote as its not correct, an off moment!

James

Interesting to note however that the OP tested with Msgbox
Worksheets("Data").Range("E1").value and did NOT get the same error message
.... which they should have if the problem is the worksheet name. I will be
interested to see what they respond to this!

Bill


"Norman Jones" wrote in message
...
Hi JJFJR,

The "Subscript out of range" message indicates a failure to find a
worksheet named 'Data'.

Check that there are no spaces at the beginning or end of the sheet's
name.


---
Regards,
Norman



"jjfjr" wrote in message
...
Hi;

i'm trying to capture a value in a range. I have the following
codeattched
to the click action on a button in my "Results" sheet:

Dim LastDataRow As Integer

LastDataRow = Worksheets("Data").Range("E1").Value

When the above line is reached, I get a "Run time error '9' Subscript out
of
range" error message.

Cell E1 of the worksheet labelled "Data" has the value 9 in it. I tried
displaying the value of it by the line :

MsgBox Worksheets("Data").Range("E1").Value

which was placed just before the problem line. When the MsgBox line
executed, the value was blank in the message box.

I'm at a loss as to what the problem is. Any help is greatly appreciated.

--
JJFJR

Hi William,

Good point.

I might assume that the sheet name had been spelled correctly in one line
and wrongly in the other, but that would not explain the empty value
returned by the Msgbox.

---
Regards,
Norman



"William Benson" wrote in message
...
Interesting to note however that the OP tested with Msgbox
Worksheets("Data").Range("E1").value and did NOT get the same error
message ... which they should have if the problem is the worksheet name. I
will be interested to see what they respond to this!

Bill


"Norman Jones" wrote in message
...
Hi JJFJR,

The "Subscript out of range" message indicates a failure to find a
worksheet named 'Data'.

Check that there are no spaces at the beginning or end of the sheet's
name.


---
Regards,
Norman

Also, while I do not have enough info to answer your stated problem, I will
caution against using integer type variables to hold row numbers except
under VERY controlled circumstances.

INTEGER: -32,768 to 32,767 [Note: Rows can go to 65,536!]
LONG: -2,147,483,648 to 2,147,483,647

"jjfjr" wrote in message
...
Hi;

i'm trying to capture a value in a range. I have the following codeattched
to the click action on a button in my "Results" sheet:

Dim LastDataRow As Integer

LastDataRow = Worksheets("Data").Range("E1").Value

When the above line is reached, I get a "Run time error '9' Subscript out
of
range" error message.

Cell E1 of the worksheet labelled "Data" has the value 9 in it. I tried
displaying the value of it by the line :

MsgBox Worksheets("Data").Range("E1").Value

which was placed just before the problem line. When the MsgBox line
executed, the value was blank in the message box.

I'm at a loss as to what the problem is. Any help is greatly appreciated.

--
JJFJR