VBA Assigning cell value to variable giving runtime error

[KeriM]

I'm trying to assign a cell value to a variable, and I keep getting
"Run-Time error '1004': Application-defined or object defined error"

I have a column number, 60, assigned to a variable calle
"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
-------------------


I've also tried it this way with the same resulting error:


Code
-------------------
ComboVal = Sheets("DATA").Cells(i, 60).value
-------------------


For some reason, this works with no error:


Code

 

[KeriM]

I'm trying to assign a cell value to a variable, and I keep getting
"Run-Time error '1004': Application-defined or object defined error"

I have a column number, 60, assigned to a variable calle
"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
-------------------

I've also tried it this way with the same resulting error:
Code

I solved my own problem. I had declared "DropDown_Txt" as a String
When I declared it as an Integer, that fixed the problem

 

[joeu2004]

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(external:=True)

The last statement might produce the same error.

 

[joeu2004]

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?