Passing a variable through a variable?

[domgilberto]

How can I do something like this:

Dim Sheet2 As String
Sheet2 = "EURUSD historical data"

Dim HighAsk1 As Worksheet
Set HighAsk1 = Sheets(Sheet2)

x = 9
HighAsk(x) = HighAsk1.Cells(x, 3) < this doesnt compile due to type mismatch

Any thoughts?

I basically want to call a variable "HighAsk(x)" inside for loops that references: Sheet2.Cells(x,y) so it makes for reading my code a lot easier!

[MrShorty]

I am not sure I understand what you want to do.

The error that statement gives me is "Sub or Function not defined" referring specifically to the "Highask(x)" part. That could be because you did not post the entire code and you have somehow defined what highask(x) means elsewhere. A type mismatch error suggests that highask() is defined or dimensioned in some way that is incompatible with that cell's value.

My first impression is that you want highask() to be an array, and you are trying to load that array with the values in column 3. Simply adding

Dim highask(1 to 10)

to the code snippet above, the code runs without error and highask(9) contains whatever is in C9 of the specified sheet.

Look at how you are dimensioning or defining highask() and see if that suggests a solution.