Convert String to a Contstant

[Guest]

to avoid being tied to a XML layout I identified each column as a Constant

Global Const CTrack1BD1 = "A"
Global Const CTrack1BD2 = "B"
Global Const CTrack2BD1 = "C"
Global Const CTrack2BD2 = "D"

and in my code I simply use eg range(CTrack1BD1 & counter) = ....

However now I want to use another cell value to slect the right Column
so cell "contains string "XXX2BD1"

My ideas was to use
TrackColumn = "CTrack" & right("XXX2BD1",4)
followed by
range("z1") = range( TrackColumn & a)

however the TrackColumn remains a sting and is not "replaced" by the Const
value "C"

any pointer would be much appriciated

Ron B

 

[Tom Ogilvy]

Maybe something like this

Sub ABC()
Dim mycol As Collection
Dim a As Long
Dim rng As Range
Set mycol = New Collection
mycol.Add "A", "CTrack1BD1"
mycol.Add "B", "CTrack1BD2"
mycol.Add "C", "CTrack2BD1"
mycol.Add "D", "CTrack2BD2"

trackcolumn = "CTrack" & Right("XXX2BD1", 4)
a = 5
Set rng = Range(mycol.Item(trackcolumn) & a)
MsgBox rng.Address

End Sub

 

[Guest]

Thanks Tom, indeed this would work....but
I think it will put to much overhead on my macro as I have to "enrich" a few
hundred events each min.

So I will stick to my workaround by calculating the column

help is appriciated though