concatenating strings to call a variable

[Frank Brown]

I have a workbook for looking up data that was pretty specialized when
I wrote it and I am trying to generalize it a little bit for other
uses.

I would like to concatenate 2 strings to make the name a variable that
is used in a range call. Complicated enough?

Currently the code is set up to read from a specific column.
"WT_Config_Col" is a string variable that is the Column of a type of
data. I would like to be able to pass "WT" to the routine below and
concatenate that string with "_Config_Col" and have that read as the
varaiable WT_Config_Col.

Sub RunComparisonWTCellCopy(rowcount)
Worksheets("run comparisons").Cells(rowcount, 1) = Range(WT_Config_Col
&
activecell.Row).Value 'copy config
end sub

I suppose it might look something like this:

dim DataSource as string
DataSource = "WT"

Sub RunComparisonWTCellCopy(rowcount, DataSource)
Worksheets("run comparisons").Cells(rowcount, 1) = Range(DataSource &
"_Config_Col" & activecell.Row).Value 'copy config
end sub

Unfortunately it will not treat 'DataSource & "_Config_Col"' as a
variable whose value is simply a string (a, b, c...)

In essence I want to concatenate 2 text strings and have excel
recognize that as a variable name and use it's variables value.

thank you for your help,
FRB

 

[Tom Ogilvy]

VB/VBA doesn't support that directly that I am aware of.

Maybe use a case statement.

 

[Frank Brown]

Thanks Tom. Specifically for this reply but also for your many other
posts that have solved lots of other VBA problems.

I sort of thought that might be the case and I have work arounds but I
was hoping to streamline some code.

Take Care,
Frank