Create a Variable Number of Variables reliant on count in vba

[dellehurley]

Hi
I was wondering if it is possible to create a variable number of variables. If I count the number of times something happens that I can create a variable that many times. ie, count =10 and variable the named variable in this case y is created 10 times = y1,y2,y3,y4 etc up to 10, or a 1000 or as many times as needed. Something like the control function.

Sub VariableVariables()
Dim x As Integer
Dim Cnt As Integer
Dim y As String

Cnt = Application.WorksheetFunction.CountIf(Ws.Range("A:A"), x)

    Do Until cnt = 0
        y& Cnt = something
        'create variable that looks something like this... y1, y2, y3 etc
        'do something
        Cnt = Cnt - 1
    Loop
End Sub

I hope this makes some sort of sense.
Dannielle

 

[dmt32]

Hi,
to do what you want use an array which can be sized dynamically to have as many elements as needed

Sub VariableVariables()
    Dim y() As Variant
    Dim Cnt As Long, x As Long, i  As Long
    Dim ws As Worksheet
   
    Set ws = ActiveSheet
   
    x = 10
  
    Cnt = Application.WorksheetFunction.CountIf(ws.Range("A:A"), x)
   
    ReDim y(1 To Cnt)
   
    For i = 1 To UBound(y)
        y(i) = i ' add value to elements
        'y(1), y(2), y(3) etc
        'do something
        MsgBox y(i)
    Next i
   
End Sub

you can read more here: Using arrays (VBA)

Dave

 

[StephenCrump]

Like this perhaps:

Dim y() As String

cnt = Application.WorksheetFunction.CountIf(ws.Range("A:A"), x)

If cnt > 1 Then
    ReDim y(1 To cnt)
    For i = 1 To UBound(y)
        'do things with y(i)
    Next i
Else
    '??
End If

 

[dellehurley]

Hi,
to do what you want use an array which can be sized dynamically to have as many elements as needed

Sub VariableVariables()
    Dim y() As Variant
    Dim Cnt As Long, x As Long, i  As Long
    Dim ws As Worksheet
  
    Set ws = ActiveSheet
  
    x = 10
 
    Cnt = Application.WorksheetFunction.CountIf(ws.Range("A:A"), x)
  
    ReDim y(1 To Cnt)
  
    For i = 1 To UBound(y)
        y(i) = i ' add value to elements
        'y(1), y(2), y(3) etc
        'do something
        MsgBox y(i)
    Next i
  
End Sub

you can read more here: Using arrays (VBA)

Dave

Thanks for your help again Dave. I knew there had to be away to get around writing to a cell or the like. Stephen's post looks like it will do the job too.
I will go and play now. Enjoy your day.
Dannielle

 

[dellehurley]

Like this perhaps:

Dim y() As String

cnt = Application.WorksheetFunction.CountIf(ws.Range("A:A"), x)

If cnt > 1 Then
    ReDim y(1 To cnt)
    For i = 1 To UBound(y)
        'do things with y(i)
    Next i
Else
    '??
End If

Hi Stephen
Thanks for your help, as I said to Dave I knew there had to be away to do this. Both of your option and Dave's look great, I will go fiddle and find which works for me best.
Enjoy your day.
Dannielle