Dear All,
I have a large number of variables that I need to indirectly reference in a series of loops.
For example if the loop count is from 1 to 100 then for each loop count the variable name has to change.
I have an example of the variable names below:
Each one of these variables acts as a counter depending on some conditions.
I have declared the following:
I have populated the the LocationName object with strings of the variable names as defined in a static array.
I start my loop
My issue is that the code fails at this step
LocationName(ArrayIndex) = LocationName(ArrayIndex) + 1
The referenced variable is defined as an Integer.
In this example the statement should be equivalent to
PTSACECriticalCount = PTSACECriticalCount + 1
Please can someone tell me what I am doing wrong with dynamically reference a variable.
Best Regards
Vonryan
I have a large number of variables that I need to indirectly reference in a series of loops.
For example if the loop count is from 1 to 100 then for each loop count the variable name has to change.
I have an example of the variable names below:
Code:
Dim PTSACECriticalCount As Integer
Dim PTSASERCriticalCount As Integer
Dim SCOLCECriticalCount As Integer
Dim SCOLSERCriticalCount As Integer
Dim SEVTCECriticalCount As Integer
Dim SEVTSERCriticalCount As Integer
Dim SHVACECriticalCount As Integer
Dim SMSACECriticalCount As Integer
Dim SMSASERCriticalCount As Integer
Dim SOVACECriticalCount As Integer
Dim SOVASERCriticalCount As Integer
Dim SEATCriticalCount As Integer
Dim PCONCriticalCount As Integer
Dim PCOECriticalCount As Integer
Each one of these variables acts as a counter depending on some conditions.
I have declared the following:
Code:
Dim StationNameArray(1, 100) As Variant
Dim LocationName As Object
Set LocationName = CreateObject("Scripting.Dictionary")
Dim LocationTagName As String
I have populated the the LocationName object with strings of the variable names as defined in a static array.
Code:
StationNameArray(1, 1) = "PTSACECriticalCount"
StationNameArray(1, 2) = "PTSASERCriticalCount"
StationNameArray(1, 3) = "SCOLCECriticalCount"
StationNameArray(1, 4) = "SCOLSERCriticalCount"
StationNameArray(1, 5) = "SEVTCECriticalCount"
StationNameArray(1, 6) = "SEVTSERCriticalCount"
StationNameArray(1, 7) = "SHVACECriticalCount"
StationNameArray(1, 8) = "SMSACECriticalCount"
StationNameArray(1, 9) = "SMSASERCriticalCount"
StationNameArray(1, 10) = "SOVACECriticalCount"
StationNameArray(1, 11) = "SOVASERCriticalCount"
StationNameArray(1, 12) = "SEATCriticalCount"
StationNameArray(1, 13) = "PCONCriticalCount"
StationNameArray(1, 14) = "PCOECriticalCount"
Dim I As Integer
Dim ArrayIndex As Integer
For I = 1 To 54
LocationName(I) = StationNameArray(1, I)
Next I
I start my loop
Code:
'Set Column First Row value
ColumnFirstRow = 2
'Set Column Last Row value
ColumnLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "R").End(-4162).Row
'COUNT THE NUMBER OF ASSIGNED OPEN DEFECTS OF CRITICAL CATAGORY
'Set the Search String values to below
SearchString1 = "Critical"
SearchString2 = "CE"
'Start Loop for the entire column
For ColumnRowCount = ColumnFirstRow To ColumnLastRow
'Set the search string to the value in Labels Column
MainString = ActiveSheet.Range("R" & ColumnRowCount)
' If the search function returns TRUE
TRUE1 = Contains(MainString, SearchString1)
TRUE2 = Contains(MainString, SearchString2)
If TRUE1 And TRUE2 Then
'AND If the STATUS column value is OPEN
If ActiveSheet.Range("D" & ColumnRowCount) = "Open" Then
MainString = ActiveSheet.Range("R" & ColumnRowCount)
If Contains(MainString, SearchString) Then
'Then add 1 to the Critical Count
LocationName(ArrayIndex) = LocationName(ArrayIndex) + 1
End If
'End IF
End If
'End If
End If
'Next Row in the LABELS column
Next ColumnRowCount
Next LocationCount
My issue is that the code fails at this step
LocationName(ArrayIndex) = LocationName(ArrayIndex) + 1
The referenced variable is defined as an Integer.
In this example the statement should be equivalent to
PTSACECriticalCount = PTSACECriticalCount + 1
Please can someone tell me what I am doing wrong with dynamically reference a variable.
Best Regards
Vonryan
Last edited by a moderator: