C
Cheer-Phil-ly
I have a situation whereby I want to count the number of records and place
the number in different variables based on two different cells in the record.
The following is a snippet of the hopefully streamlined code. This will
eleviate a lot of duplicate code by putting the If|Else Statements in a sub
routine and and passing some information to that sub and have it count and
put the count in the variable. But I have to build the name of the variable
in the sub and then get them to reference the actual variables... is this
possible?
Dim Type1var1, Type2var2, Type3var3 as integer 'in General Declarations
sub Populate
For Each cell In Range("List")
status = cell.Offset(0, 5)
Select Case cell.Offset(0, 27)
'Type1
Case 888, 889
Call CalcOrig("Type1", status)
'Type2
Case 893, 894
Call CalcOrig("Type2", status)
Case 900 to 905
Call CalcOrig("Type3",status)
End Select
Next
end sub
sub CalcOrig(prefix as string, status as string)
If status = "Accepted" Or _
status = "Approved - override" Or _
status = "Auto-Approve" Then
newvar = prefix & "var1" ' I want this to reference the variable
' Type1var1... IS THIS
POSSIBLE?
newvar = newvar + 1
ElseIf status = "Approved-Counteroffer" _
Or status = "Qualified-Workout" Then
newvar = prefix & "var2" ' I want this to reference the variable
' Type1var2... IS THIS
POSSIBLE?
newvar = newvar + 1
ElseIf status = "Declined" Or _
status = "Declined - override" Then
newvar = prefix & "var3" ' I want this to reference the variable
' Type1var3... IS THIS
POSSIBLE?
newvar = newvar + 1
End If
end sub
the number in different variables based on two different cells in the record.
The following is a snippet of the hopefully streamlined code. This will
eleviate a lot of duplicate code by putting the If|Else Statements in a sub
routine and and passing some information to that sub and have it count and
put the count in the variable. But I have to build the name of the variable
in the sub and then get them to reference the actual variables... is this
possible?
Dim Type1var1, Type2var2, Type3var3 as integer 'in General Declarations
sub Populate
For Each cell In Range("List")
status = cell.Offset(0, 5)
Select Case cell.Offset(0, 27)
'Type1
Case 888, 889
Call CalcOrig("Type1", status)
'Type2
Case 893, 894
Call CalcOrig("Type2", status)
Case 900 to 905
Call CalcOrig("Type3",status)
End Select
Next
end sub
sub CalcOrig(prefix as string, status as string)
If status = "Accepted" Or _
status = "Approved - override" Or _
status = "Auto-Approve" Then
newvar = prefix & "var1" ' I want this to reference the variable
' Type1var1... IS THIS
POSSIBLE?
newvar = newvar + 1
ElseIf status = "Approved-Counteroffer" _
Or status = "Qualified-Workout" Then
newvar = prefix & "var2" ' I want this to reference the variable
' Type1var2... IS THIS
POSSIBLE?
newvar = newvar + 1
ElseIf status = "Declined" Or _
status = "Declined - override" Then
newvar = prefix & "var3" ' I want this to reference the variable
' Type1var3... IS THIS
POSSIBLE?
newvar = newvar + 1
End If
end sub