Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting inconsistent Error 91-Object variable or With block variable not set | Excel Programming | |||
Run-time Error'91: Object variable or With block variable not set | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |