Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning cell values to variables | Excel Discussion (Misc queries) | |||
MS Office Excel 2003 assigning cell values to variables | Excel Programming | |||
Assigning multiple text strings to variables | Excel Programming | |||
Assigning cell address components to variables | Excel Programming | |||
Assigning values from a selected range to individual variables | Excel Programming |