getting value of variable name

[Don Johnson]

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?

[Die_Another_Day]

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?

[NickHK]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>
>
>
>
>

[Don Johnson]

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?
>
>

[Don Johnson]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> >
> >
> >
> >
> >
>
>
>