getting value of variable name
 

I have a macro where I am pulling data from numerous worksheets in a single
workbook to approximately 200 variables that are not written anywhere on a
spreadsheet. Now I am near the end of my macro and want to record the
variables to a spreadsheet if it passes some logical tests. Below is a very
simplified example of my problem.

Dim ccy1 As Currency
Dim ccy2 As Currency
Dim varOutput As Variant
Dim intCounter As Integer
Dim strFormula As String

'There is no relationship between the values in ccy1 and ccy2
ccy1 = 3
ccy2 = 6

For intCounter = 1 To 2
strZ(intCounter) = "ccy" & Right(Str(intCounter), 1)
Next intCounter
' thus giving me strZ(1) = "ccy1" and strZ(2) = "ccy2"


'Now I want to the write the values of ccy1 and ccy2 to a spreadsheet so
cell 3
'"A1" = 3 and "A2" = 6 using some permutation of strZ(intCounter)

For intCounter = 1 To 2
Cells(intCounter,1).Formula = ? strZ(intCounter)
Next intCounter

I thought I had done this before but I can't seemed to come up with a
solution now.

Any suggestions?

getting value of variable name
 

Try this:
For intCounter = 1 To 2
Cells(intCounter,1).Formula = ccy & intCounter
Next intCounter

HTH

Die_Another_Day
Don Johnson wrote:
I have a macro where I am pulling data from numerous worksheets in a single
workbook to approximately 200 variables that are not written anywhere on a
spreadsheet. Now I am near the end of my macro and want to record the
variables to a spreadsheet if it passes some logical tests. Below is a very
simplified example of my problem.

Dim ccy1 As Currency
Dim ccy2 As Currency
Dim varOutput As Variant
Dim intCounter As Integer
Dim strFormula As String

'There is no relationship between the values in ccy1 and ccy2
ccy1 = 3
ccy2 = 6

For intCounter = 1 To 2
strZ(intCounter) = "ccy" & Right(Str(intCounter), 1)
Next intCounter
' thus giving me strZ(1) = "ccy1" and strZ(2) = "ccy2"


'Now I want to the write the values of ccy1 and ccy2 to a spreadsheet so
cell 3
'"A1" = 3 and "A2" = 6 using some permutation of strZ(intCounter)

For intCounter = 1 To 2
Cells(intCounter,1).Formula = ? strZ(intCounter)
Next intCounter

I thought I had done this before but I can't seemed to come up with a
solution now.

Any suggestions?

getting value of variable name
 

Basically you can't refer to variable names as string as you are attempting
to do.
The easiest solution would be to use an array and loop through the elements.
e.g
Dim ccy() as Currency
ReDim ccy(Range("A1").Value) 'Or however you need
If you beforehand how many you need
Dim ccy(1 to 2) as Currency

Then you can loop, setting values:
Dim i as Long
For i=Lbound(ccy) to Ubound(ccy)
Range("a1").offset(i-1,0).value=ccy(i)
Next

NickHK

"Don Johnson" wrote in message
...
I have a macro where I am pulling data from numerous worksheets in a
single
workbook to approximately 200 variables that are not written anywhere on a
spreadsheet. Now I am near the end of my macro and want to record the
variables to a spreadsheet if it passes some logical tests. Below is a
very
simplified example of my problem.

Dim ccy1 As Currency
Dim ccy2 As Currency
Dim varOutput As Variant
Dim intCounter As Integer
Dim strFormula As String

'There is no relationship between the values in ccy1 and ccy2
ccy1 = 3
ccy2 = 6

For intCounter = 1 To 2
strZ(intCounter) = "ccy" & Right(Str(intCounter), 1)
Next intCounter
' thus giving me strZ(1) = "ccy1" and strZ(2) = "ccy2"


'Now I want to the write the values of ccy1 and ccy2 to a spreadsheet so
cell 3
'"A1" = 3 and "A2" = 6 using some permutation of strZ(intCounter)

For intCounter = 1 To 2
Cells(intCounter,1).Formula = ? strZ(intCounter)
Next intCounter

I thought I had done this before but I can't seemed to come up with a
solution now.

Any suggestions?

getting value of variable name
 

Thank you for your suggestion but this did not work. All I endered up getting
entered into the cells were "1" and "2", the value of intCounter.

Thank you again for your effort.

"Die_Another_Day" wrote:

Try this:
For intCounter = 1 To 2
Cells(intCounter,1).Formula = ccy & intCounter
Next intCounter

HTH

Die_Another_Day
Don Johnson wrote:
I have a macro where I am pulling data from numerous worksheets in a single
workbook to approximately 200 variables that are not written anywhere on a
spreadsheet. Now I am near the end of my macro and want to record the
variables to a spreadsheet if it passes some logical tests. Below is a very
simplified example of my problem.

Dim ccy1 As Currency
Dim ccy2 As Currency
Dim varOutput As Variant
Dim intCounter As Integer
Dim strFormula As String

'There is no relationship between the values in ccy1 and ccy2
ccy1 = 3
ccy2 = 6

For intCounter = 1 To 2
strZ(intCounter) = "ccy" & Right(Str(intCounter), 1)
Next intCounter
' thus giving me strZ(1) = "ccy1" and strZ(2) = "ccy2"


'Now I want to the write the values of ccy1 and ccy2 to a spreadsheet so
cell 3
'"A1" = 3 and "A2" = 6 using some permutation of strZ(intCounter)

For intCounter = 1 To 2
Cells(intCounter,1).Formula = ? strZ(intCounter)
Next intCounter

I thought I had done this before but I can't seemed to come up with a
solution now.

Any suggestions?

getting value of variable name
 

Thanks for your suggestion. I will see if I can make this work for me.

"NickHK" wrote:

Basically you can't refer to variable names as string as you are attempting
to do.
The easiest solution would be to use an array and loop through the elements.
e.g
Dim ccy() as Currency
ReDim ccy(Range("A1").Value) 'Or however you need
If you beforehand how many you need
Dim ccy(1 to 2) as Currency

Then you can loop, setting values:
Dim i as Long
For i=Lbound(ccy) to Ubound(ccy)
Range("a1").offset(i-1,0).value=ccy(i)
Next

NickHK

"Don Johnson" wrote in message
...
I have a macro where I am pulling data from numerous worksheets in a
single
workbook to approximately 200 variables that are not written anywhere on a
spreadsheet. Now I am near the end of my macro and want to record the
variables to a spreadsheet if it passes some logical tests. Below is a
very
simplified example of my problem.

Dim ccy1 As Currency
Dim ccy2 As Currency
Dim varOutput As Variant
Dim intCounter As Integer
Dim strFormula As String

'There is no relationship between the values in ccy1 and ccy2
ccy1 = 3
ccy2 = 6

For intCounter = 1 To 2
strZ(intCounter) = "ccy" & Right(Str(intCounter), 1)
Next intCounter
' thus giving me strZ(1) = "ccy1" and strZ(2) = "ccy2"


'Now I want to the write the values of ccy1 and ccy2 to a spreadsheet so
cell 3
'"A1" = 3 and "A2" = 6 using some permutation of strZ(intCounter)

For intCounter = 1 To 2
Cells(intCounter,1).Formula = ? strZ(intCounter)
Next intCounter

I thought I had done this before but I can't seemed to come up with a
solution now.

Any suggestions?