Variable Name typed in Cell can be used in VBA or not?

[Ashish8859]

Hi ,


I am new to vba. I have simple thing to do but don't know how to do it. I have a simple code which is as follows:
Sub Test()

Dim Test1 As Long
Dim Test2 As String


Test1 = Range("J7") + Range("J8")

Test2 = Range("K7") + 2

MsgBox Test2

End Sub

I have define two variable Test1 & Test2. Test1 is simple addition of J7 + J8 (J7 = 1, J8 = 2) = 3. Then i have written Test1 in Cell K7 and in Test2 i have written the above formula. The answer must be 3 + 2 = 5, but when i ran the code it give error as Error 13 Type Mismatch.

Please can some one help me out with this. This is just a test check, actually i have written more than 20 variable names in excell cells and i want them to be used in Excel vba. What it should exactly do is treat Range("K7") as Test1 and take the result of Test1 i.e. 3 and add 2 to it.

if i use Test2 = Test1 + 2 then it give 5 as result, but when i reference Test1 from the cell K7 it gives error.


Thanks & regards,

Ashish

 

[jasonb75]

That can not be done, the variable names need to be set in vba, not in a cell.

There might be ways to work around it using different methods but I wouldn't recommend trying it.

 

[Ashish8859]

Thank for the reply.........

But i can you tell me the work around please.........


It will be a great help for me.........

Thanks & regards,

Ashish

That can not be done, the variable names need to be set in vba, not in a cell.

There might be ways to work around it using different methods but I wouldn't recommend trying it.

 

[jasonb75]

But i can you tell me the work around please.........

I didn't say that there is a workaround, I said that there might be a workaround. There is a difference!

The initial idea that I had was to assign the calculations to named ranges, but giving it further thought, it is not going to work.