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 |
Quote:
|
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. |
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? |
All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com