Quick help needed: variable within a variable...

[bapcki1]

Hi Guys!

I would like to declare a variable that uses a value from another variable. (is this known as dynamic variables?)

The offending line is:

Wbq = ThisWorkbook.Sheets("Sheet 1").Cells(NumEntry, 2).Value

where NumEntry is the variable within another variable, Wbq.

It looks like this in the whole code text:

Sub Collation()

ThisWorkbook.Sheets("Collation").Range("B2:F12").ClearContents

Dim DiffcalcWb As Integer
Dim Wborig As Integer
Dim Wbq As Integer

NumEntry = ThisWorkbook.Sheets("Collation").Range("L2").Value
Wborig = ThisWorkbook.Sheets("Sheet 1").Range("B2").Value
Wbq = ThisWorkbook.Sheets("Sheet 1").Cells(NumEntry, 2).Value
DiffcalcWb = Wborig - Wbq
Sheets("Collation").Select
Range("B2").Value = DiffcalcWb

Can anyone help me please?


Paddy

 

[Dryver14]

If sheet1 is naturally listed it will be Sheet1 not sheet 1

 

[bapcki1]

Hi Dryver,

Thanks, but the problem of naming the variable within a variable still remains..

Any ideas?


paddy

 

[shg]

Where is NumEntry declared?

 

[Dryver14]

this should not cause a problem assuming numentry is a number

what is numentry returning?

 

[bapcki1]

Hi Shg,

Sorry I was typing out the code manually, and I made a couple of mistakes but the naming a variable within a variable problem still isn't working...

I would have declared NumEntry:

Dim NumEntry As Integer

 

[bapcki1]

Hi Dryver,

when I type

? NumEntry

into the immediate window and hit return, nothing happens. NumEntry should equal 3.


Thanks for your help - I really appreciate it.

Paddy

 

[bapcki1]

*Correcting the left out lines*

Sub Collation()

ThisWorkbook.Sheets("Collation").Range("B2:F12").ClearContents

Dim DiffcalcWb As Integer
Dim Wborig As Integer
Dim Wbq As Integer
Dim NumEntry As Integer

NumEntry = ThisWorkbook.Sheets("Collation").Range("L2").Value
Wborig = ThisWorkbook.Sheets("Sheet1").Range("B2").Value
Wbq = ThisWorkbook.Sheets("Sheet1").Cells(NumEntry, 2).Value
DiffcalcWb = Wborig - Wbq
Sheets("Collation").Select
Range("B2").Value = DiffcalcWb

 

[bapcki1]

this is what the immediate window comes up with:

?ThisWorkbook.Sheets("Collation").Range("J2").Value
 3 
?NumEntry = ThisWorkbook.Sheets("Collation").Range("J2").Value
False
?NumEntry

I have declared the variable as:

Sub AmazonCollation()
Dim NumEntry As Integer
NumEntry = ThisWorkbook.Sheets("Collation").Range("J2").Value

?????

so confused, please help..


Paddy

 

[ScottR]

You're not "naming a variable within another variable". I don't even know what that's supposed to mean. You're using a variable in very standard way.

Post your code as it exists or you risk getting an incorrect answer. We don't want to be guessing that you have variable scope correct, for example (ie declared within the same sub).

Typing ?variable into the immediate window after the procedure has run will only return a value if the variable still has scope - ie has been declared at the module level which you are indicating is not the case

add this line

NumEntry = ThisWorkbook.Sheets("Collation").Range("L2").Value
[B]debug.print NumEntry[/B]

will return the actual value at runtime.

Also specify the error you're receiving.

My guess would be that "L2" contains a zero or zero equivalent value.