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

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

Maybe use a case statement.

--
Regards,
Tom Ogilvy

"Frank Brown" wrote in message
om...
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

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


"Tom Ogilvy" wrote in message ...
VB/VBA doesn't support that directly that I am aware of.

Maybe use a case statement.

--
Regards,
Tom Ogilvy

"Frank Brown" wrote in message
om...
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