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

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


--
Regards,
Tom Ogilvy



"Ron B" <(E-Mail Removed)> wrote in message
news:8590C8B5-702C-4608-BAFC-(E-Mail Removed)...
> 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

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

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ron B" <(E-Mail Removed)> wrote in message
> news:8590C8B5-702C-4608-BAFC-(E-Mail Removed)...
> > 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
>
>
>