REPOSTED FOR CLARIFICATION ( MS Office Excel 2003 )
temp = Sheets("Sheet1").Cells(Y, X).Value2
temp = Cells(Y, X).Value2
temp = Sheets("Sheet1").Range(Cells(Y, X), Cells(Y, X)).Value2

All 3 statements produce a 1004 runtime error. Use "Sheets" or
"Worksheets" makes no difference either.

Except for temp all variables are non-zero integers, and temp is
not declared (for now) so that Excel VB can determine the type as
needed.

None of the "Cells" referred to are "CurCell": not Active nor Selected
( that I know of )
I simply want to "point" to them i.e. to read them.
I believe even the Offset Method (?) changes the selected cell.

I don't want to employ the standard Range("A1") object since the A1 is
a literal string,
and I need more programatic control hence the Cells method.

Thanks, Mike
PS I've seen online doc with the same statements indicated that they
should work.

works for me:

Sub test()
x = 1
y = 1
Cells(y, x) = 30
temp = Sheets("Sheet1").Cells(y, x).Value2
temp = Cells(y, x).Value2
temp = Sheets("Sheet1").Range(Cells(y, x), Cells(y, x)).Value2
End Sub

What are the values of x and y ? What value do you have in the cell
(Cells(y,x)) ? Why are you using Value2 rather than just Value or
defaulting ?

Regards

Trevor


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> REPOSTED FOR CLARIFICATION ( MS Office Excel 2003 )
> temp = Sheets("Sheet1").Cells(Y, X).Value2
> temp = Cells(Y, X).Value2
> temp = Sheets("Sheet1").Range(Cells(Y, X), Cells(Y, X)).Value2
>
> All 3 statements produce a 1004 runtime error. Use "Sheets" or
> "Worksheets" makes no difference either.
>
> Except for temp all variables are non-zero integers, and temp is
> not declared (for now) so that Excel VB can determine the type as
> needed.
>
> None of the "Cells" referred to are "CurCell": not Active nor Selected
> ( that I know of )
> I simply want to "point" to them i.e. to read them.
> I believe even the Offset Method (?) changes the selected cell.
>
> I don't want to employ the standard Range("A1") object since the A1 is
> a literal string,
> and I need more programatic control hence the Cells method.
>
> Thanks, Mike
> PS I've seen online doc with the same statements indicated that they
> should work.
>

Trevor,

Thanks - that's good news. All x,y data should be non-zero integer
( For ...Next etc.). The Cell data is also integer.
I used Value2 out of desperation as it does not support dates etc, and
I was only dealing with integers.

I'll try your code, and I'll probably find I have a problem elsewhere.
( believe it or not this is my first time on a ...dare I use the old
term ....bulletin board )

On Mar 28, 4:53 pm, "Trevor Shuttleworth" <Tre...@Shucks.demon.co.uk>
wrote:
> works for me:
>
> Sub test()
> x = 1
> y = 1
> Cells(y, x) = 30
> temp = Sheets("Sheet1").Cells(y, x).Value2
> temp = Cells(y, x).Value2
> temp = Sheets("Sheet1").Range(Cells(y, x), Cells(y, x)).Value2
> End Sub
>
> What are the values of x and y ? What value do you have in the cell
> (Cells(y,x)) ? Why are you using Value2 rather than just Value or
> defaulting ?
>
> Regards
>
> Trevor
>
> <m...@infinitesign.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > REPOSTED FOR CLARIFICATION ( MS Office Excel 2003 )
> > temp = Sheets("Sheet1").Cells(Y, X).Value2
> > temp = Cells(Y, X).Value2
> > temp = Sheets("Sheet1").Range(Cells(Y, X), Cells(Y, X)).Value2
>
> > All 3 statements produce a 1004 runtime error. Use "Sheets" or
> > "Worksheets" makes no difference either.
>
> > Except for temp all variables are non-zero integers, and temp is
> > not declared (for now) so that Excel VB can determine the type as
> > needed.
>
> > None of the "Cells" referred to are "CurCell": not Active nor Selected
> > ( that I know of )
> > I simply want to "point" to them i.e. to read them.
> > I believe even the Offset Method (?) changes the selected cell.
>
> > I don't want to employ the standard Range("A1") object since the A1 is
> > a literal string,
> > and I need more programatic control hence the Cells method.
>
> > Thanks, Mike
> > PS I've seen online doc with the same statements indicated that they
> > should work.- Hide quoted text -
>
> - Show quoted text -

Mike,
Using "Sheet1", X and Y are valid, methods 1 & 2 will work.
Method 3 may work depending on the location of the code/Active sheet, as
Cells may not be on the "Sheet1".
But this will work, note the qualifying "." before Cells:
With Sheets("Sheet1")
temp = .Range(.Cells(Y, X), .Cells(Y, X)).Value2
End With

Maybe this will help:

'On Sheet 1
Private Sub CommandButton1_Click()

Worksheets(1).Cells(1, 1).Value = "Sheet 1"
Worksheets(2).Cells(1, 1).Value = "Sheet 2"

MsgBox Cells(1, 1).Value
MsgBox GetValue()

Worksheets(2).Select

MsgBox Cells(1, 1).Value
MsgBox GetValue()
MsgBox ActiveSheet.Cells(1, 1).Value

End Sub

'---------------------
'On a module
Public Function GetValue() As String
GetValue = Cells(1, 1).Value
End Function

Having said that it is seldom necessary to .Select object before using them.
Personally, I always fully qualify the objects, unless the aim is to always
work on the active sheet or sheet with the code.

NickHK

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> REPOSTED FOR CLARIFICATION ( MS Office Excel 2003 )
> temp = Sheets("Sheet1").Cells(Y, X).Value2
> temp = Cells(Y, X).Value2
> temp = Sheets("Sheet1").Range(Cells(Y, X), Cells(Y, X)).Value2
>
> All 3 statements produce a 1004 runtime error. Use "Sheets" or
> "Worksheets" makes no difference either.
>
> Except for temp all variables are non-zero integers, and temp is
> not declared (for now) so that Excel VB can determine the type as
> needed.
>
> None of the "Cells" referred to are "CurCell": not Active nor Selected
> ( that I know of )
> I simply want to "point" to them i.e. to read them.
> I believe even the Offset Method (?) changes the selected cell.
>
> I don't want to employ the standard Range("A1") object since the A1 is
> a literal string,
> and I need more programatic control hence the Cells method.
>
> Thanks, Mike
> PS I've seen online doc with the same statements indicated that they
> should work.
>