Assigning Cell data to VB Variables
 

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.

Assigning Cell data to VB Variables
 

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


wrote in message
oups.com...
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.

Assigning Cell data to VB Variables
 

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"
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

wrote in message

oups.com...



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 -

Assigning Cell data to VB Variables
 

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

wrote in message
oups.com...
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.