Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA Assigning cell value to variable giving runtime error
I'm trying to assign a cell value to a variable, and I keep getting a "Run-Time error '1004': Application-defined or object defined error"
I have a column number, 60, assigned to a variable called "DropDown_txt" I have a row value assigned to a variable called "i" I would like to assign the value in that cell reference to a variable. Here is my code that is giving me the above mentioned error: Code:
ComboVal = Sheets("DATA").Cells(i,DropDown_txt).value Code:
ComboVal = Sheets("DATA").Cells(i, 60).value Code:
ComboVal = Sheets("DATA").Cells(i, "BH").value |
#2
|
|||
|
|||
Quote:
Last edited by KeriM : November 22nd 13 at 06:47 PM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Assigning cell value to variable giving runtime error
"KeriM" wrote:
I keep getting a "Run-Time error '1004': Application-defined or object defined error" [....] Here is my code that is giving me the above mentioned error: [....] ComboVal = Sheets("DATA").Cells(i,DropDown_txt).value [....] I've also tried it this way with the same resulting error: [....] ComboVal = Sheets("DATA").Cells(i, 60).value [....] For some reason, this works with no error: [....] ComboVal = Sheets("DATA").Cells(i, "BH").value [....] Does anyone have any suggestions as to why this is happening? Since "BH" is indeed column 60, Cells(i,60) should work as well as Cells(i,"BH") for the same value of i. I can only imagine the values of i and DropDown_txt are not what you expect at the time of the error -- or you inadvertently corrected your mistake when you retyped the examples in your posting. (I know you retyped the examples because .value would appear as .Value in the VBA editor after enter the statement. In the future, copy-and-paste into your posting. FYI, .Value is not needed in this context.) Add the following code and verify your assumptions. On Error Go To oops ' or do this once much earlier ComboVal = Sheets("DATA").Cells(i,DropDown_txt).value On Error Go To 0 ' optional [... rest of your code ....] Exit Sub oops: Stop End Sub If you reach the Stop statement, press ctrl+G to open the Immediate Window, and do the following: Print i, DropDown_txt Print Sheets("DATA").Cells(i,DropDown_txt).Address(exter nal:=True) The last statement might produce the same error. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Assigning cell value to variable giving runtime error
"KeriM" wrote:
Here is my code that is giving me the above mentioned error: [....] ComboVal = Sheets("DATA").Cells(i,DropDown_txt).value [....] I've also tried it this way with the same resulting error: [....] ComboVal = Sheets("DATA").Cells(i, 60).value [....] I solved my own problem. I had declared "DropDown_Txt" as a String. When I declared it as an Integer, that fixed the problem. That does not explain why Cells(i,60) failed. Did you mistype the example in your posting, inadvertently correcting the syntax error, or otherwise misrepresent the facts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trouble assigning array to variable (variant) runtime error 9 please help (ignore correction, this didn't appear for some reason) | Excel Programming | |||
Runtime error 9 subscript out of range - assigning array to variable | Excel Programming | |||
Macro giving runtime error on one PC and not another | Excel Programming | |||
Macro giving runtime error on one PC and not another | Excel Programming | |||
Excel help giving runtime error | Excel Discussion (Misc queries) |