VBA String Reference A Double Variable

[CmCalef]

Hello,

I have searched the forums extensively and not found an answer. It seems like this would be a common questions, but I can't find it:

Sub StoreDMAInfo()
Dim Info As String
Dim ColNumberString As String
Dim ColNumber As Double

ColTotalNetSales = 4
Info = "TotalNetSales"


ColNumberString = "Col" & Info
ColNumber = Val(ColNumberString)

I want ColNumber to be equal to 4. This does not work. In a workbook this would be accomplished by using =INDIRECT("Col" & Info)

Info will be set by user input.

Any thoughts? Thank you very much.

-Chris

 

[Norie]

Chris

That just won't work in VBA.

The INDIRECT would only work on the worksheet if you had a named range 'ColTotalNetSales'.

It's not possible to use create/refer to/whatever a variable in the same way.