Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store values in variables based on count
I'm trying to write a macro that declares variables in a dim statement where
the number of variables are based on a count (number of cells in a range for example). To illustrate I present the following macro : Sub SetVariableNames() Dim myVarRange As Range Dim myCell(1 To 6) As Variant Dim x As Integer Dim i As Integer 'Assigns values to myCell based on contents of myRange i = 1 x = ActiveSheet.Range("myRange").Count Set myVarRange = Worksheets(1).Range("myRange") For i = 1 To x myCell(i) = myVarRange.Cells(i).Value Next i End Sub The above macro work fine becasue I specified in the dim statement that I had 6 values to assign to myCell(1 to 6). However, I would like the macro to set the upper range based on the count of cells in the selected range so that values could be stored to a variable based on each value in the range which could change. I can't figure out how to do that. The variables would be used elsewhere in the routine woth an action based on the variable's value. I originally tried myCell(1 to X) but I get a variable not defined error. Thanks in advance for the help! Microsmith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store values in variables based on count
You could use ReDim:
Dim myCell As Variant Dim x As Long x = ActiveSheet.Range("myRange").Count ReDim myCell(1 to x) but you might also want to consider simply using myCell as a two-dimensional array): Dim myCell As Variant Dim i As Long myCell = Worksheets(1).Range("myRange").Value For i = 1 To UBound(myCell, 1) Debug.Print myCell(i, 1) Next i In article , Microsmith wrote: I'm trying to write a macro that declares variables in a dim statement where the number of variables are based on a count (number of cells in a range for example). To illustrate I present the following macro : Sub SetVariableNames() Dim myVarRange As Range Dim myCell(1 To 6) As Variant Dim x As Integer Dim i As Integer 'Assigns values to myCell based on contents of myRange i = 1 x = ActiveSheet.Range("myRange").Count Set myVarRange = Worksheets(1).Range("myRange") For i = 1 To x myCell(i) = myVarRange.Cells(i).Value Next i End Sub The above macro work fine becasue I specified in the dim statement that I had 6 values to assign to myCell(1 to 6). However, I would like the macro to set the upper range based on the count of cells in the selected range so that values could be stored to a variable based on each value in the range which could change. I can't figure out how to do that. The variables would be used elsewhere in the routine woth an action based on the variable's value. I originally tried myCell(1 to X) but I get a variable not defined error. Thanks in advance for the help! Microsmith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store values in variables based on count
Use a dynamic array.
Dim myCell() as Variant then after you've set your x variable based on the count, add this line Redim myCell(1 to x) "Microsmith" wrote: I'm trying to write a macro that declares variables in a dim statement where the number of variables are based on a count (number of cells in a range for example). To illustrate I present the following macro : Sub SetVariableNames() Dim myVarRange As Range Dim myCell(1 To 6) As Variant Dim x As Integer Dim i As Integer 'Assigns values to myCell based on contents of myRange i = 1 x = ActiveSheet.Range("myRange").Count Set myVarRange = Worksheets(1).Range("myRange") For i = 1 To x myCell(i) = myVarRange.Cells(i).Value Next i End Sub The above macro work fine becasue I specified in the dim statement that I had 6 values to assign to myCell(1 to 6). However, I would like the macro to set the upper range based on the count of cells in the selected range so that values could be stored to a variable based on each value in the range which could change. I can't figure out how to do that. The variables would be used elsewhere in the routine woth an action based on the variable's value. I originally tried myCell(1 to X) but I get a variable not defined error. Thanks in advance for the help! Microsmith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store values in variables based on count
Thanks JLatham and JE McGimpsey. This is exactly what I needed!
"JE McGimpsey" wrote: You could use ReDim: Dim myCell As Variant Dim x As Long x = ActiveSheet.Range("myRange").Count ReDim myCell(1 to x) but you might also want to consider simply using myCell as a two-dimensional array): Dim myCell As Variant Dim i As Long myCell = Worksheets(1).Range("myRange").Value For i = 1 To UBound(myCell, 1) Debug.Print myCell(i, 1) Next i In article , Microsmith wrote: I'm trying to write a macro that declares variables in a dim statement where the number of variables are based on a count (number of cells in a range for example). To illustrate I present the following macro : Sub SetVariableNames() Dim myVarRange As Range Dim myCell(1 To 6) As Variant Dim x As Integer Dim i As Integer 'Assigns values to myCell based on contents of myRange i = 1 x = ActiveSheet.Range("myRange").Count Set myVarRange = Worksheets(1).Range("myRange") For i = 1 To x myCell(i) = myVarRange.Cells(i).Value Next i End Sub The above macro work fine becasue I specified in the dim statement that I had 6 values to assign to myCell(1 to 6). However, I would like the macro to set the upper range based on the count of cells in the selected range so that values could be stored to a variable based on each value in the range which could change. I can't figure out how to do that. The variables would be used elsewhere in the routine woth an action based on the variable's value. I originally tried myCell(1 to X) but I get a variable not defined error. Thanks in advance for the help! Microsmith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count w/ multiple variables & text values | Excel Worksheet Functions | |||
count unique values if 2 variables | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
Count rows based on multiple variables | Excel Programming | |||
Store variables between XLA sessions | Excel Programming |