Concatenate String with Counter to access variable Excel VBA 4

[JasonEnsor]

Hi Guys,

I have the following declared in my excel workbook, as my case statement loops through several faculties i would like to reduce the amount of repeated code. I was hoping to be able to access a variable by concatenating a string with a counter variable. i have done this using controls on a userform but can not workout how to do this for variable values.

I would like to concatenate "cw" with a counter value to then access the cw1, cw2, cw3 values

Any help would be appreciated

J.

    cw1 = ThisWorkbook.Sheets("Sheet1").Range("F" & currentRow)
    cw2 = ThisWorkbook.Sheets("Sheet1").Range("H" & currentRow)
    cw3 = ThisWorkbook.Sheets("Sheet1").Range("J" & currentRow)
    ex1 = ThisWorkbook.Sheets("Sheet1").Range("L" & currentRow)
    ex2 = ThisWorkbook.Sheets("Sheet1").Range("N" & currentRow)
    ex3 = ThisWorkbook.Sheets("Sheet1").Range("P" & currentRow)

Select Case faculty
        Case "ADT"
            For Counter = 1 To 3
                cName = ("cw" & Counter)
                If cName <> "" And cName < 20 Then
                    Select Case semester
                    Case "A"
                        AdtA = AdtA + 1
                    Case "S"
                        AdtS = AdtS + 1
                    Case "T"
                        AdtT = AdtT + 1
                    Case Else
                        AdtO = AdtO + 1
                    End Select
                End If
                
                cNameEx = "ex" & Counter
                
                If cNameEx <> "" And cNameEx < 20 Then
                    Select Case semester
                    Case "A"
                        AdtAE = AdtAE + 1
                    Case "S"
                        AdtSE = AdtSE + 1
                    Case "T"
                        AdtTE = AdtTE + 1
                    Case Else
                        AdtOE = AdtOE + 1
                    End Select
                End If
            Next Counter
.....

 

[SkipVought]

hi,

for instance...

   cName = ThisWorkbook.Sheets("Sheet1").Range("F" & currentRow).Offset(counter - 1) * 2

Your method will not work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

If you want indexed variables, use an Array.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Hi Guys,

Thanks for the suggestions so far, i am posting my code as it is now, in the hope that one of you guys will be able to help me shorten the repetition in it....and remove the need for concatenating the straing and counter to access a variable.

Sub FindData()

' Totals
Dim AdtTotal As Integer: Dim EhsTotal As Integer
Dim BclTotal As Integer: Dim UdbTotal As Integer
Dim UdcTotal As Integer: Dim UdolTotal As Integer

' Faculty Autumn
Dim AdtA As Integer: Dim EhsA As Integer: Dim BclA As Integer
Dim UdbA As Integer: Dim UdcA As Integer: Dim UdolA As Integer

' Faculty Thru
Dim AdtT As Integer: Dim EhsT As Integer: Dim BclT As Integer
Dim UdbT As Integer: Dim UdcT As Integer: Dim UdolT As Integer

' Faculty Spring
Dim AdtS As Integer: Dim EhsS As Integer: Dim BclS As Integer
Dim UdbS As Integer: Dim UdcS As Integer: Dim UdolS As Integer

' Faculty Other
Dim AdtO As Integer: Dim EhsO As Integer: Dim BclO As Integer
Dim UdbO As Integer: Dim UdcO As Integer: Dim UdolO As Integer

' Faculty Autumn
Dim AdtAE As Integer: Dim EhsAE As Integer: Dim BclAE As Integer
Dim UdbAE As Integer: Dim UdcAE As Integer: Dim UdolAE As Integer

' Faculty Thru
Dim AdtTE As Integer: Dim EhsTE As Integer: Dim BclTE As Integer
Dim UdbTE As Integer: Dim UdcTE As Integer: Dim UdolTE As Integer

' Faculty Spring
Dim AdtSE As Integer: Dim EhsSE As Integer: Dim BclSE As Integer
Dim UdbSE As Integer: Dim UdcSE As Integer: Dim UdolSE As Integer

' Faculty Other
Dim AdtOE As Integer: Dim EhsOE As Integer: Dim BclOE As Integer
Dim UdbOE As Integer: Dim UdcOE As Integer: Dim UdolOE As Integer
Dim cw1 As String: Dim cw2 As String: Dim cw3 As String
Dim ex1 As String: Dim ex2 As String: Dim ex3 As String

Dim faculty As String

    For Each rdata In ThisWorkbook.Sheets("Sheet1").Range("A2:A7502")
        
        rdata.Rows.EntireRow.Select
        currentRow = ActiveCell.Row
        
        faculty = ThisWorkbook.Sheets("Sheet1").Range("T" & currentRow)
        semester = ThisWorkbook.Sheets("Sheet1").Range("E" & currentRow)
        
        cw1 = ThisWorkbook.Sheets("Sheet1").Range("F" & currentRow)
        cw2 = ThisWorkbook.Sheets("Sheet1").Range("H" & currentRow)
        cw3 = ThisWorkbook.Sheets("Sheet1").Range("J" & currentRow)
        ex1 = ThisWorkbook.Sheets("Sheet1").Range("L" & currentRow)
        ex2 = ThisWorkbook.Sheets("Sheet1").Range("N" & currentRow)
        ex3 = ThisWorkbook.Sheets("Sheet1").Range("P" & currentRow)
        
        Select Case faculty
            Case "ADT"
                'Coursework
                    If Len(cw1) > 0 Then
                        If cw1 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtA = AdtA + 1
                                Case "S"
                                    AdtS = AdtS + 1
                                Case "T"
                                    AdtT = AdtT + 1
                                Case Else
                                    AdtO = AdtO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(cw2) > 0 Then
                        If cw2 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtA = AdtA + 1
                                Case "S"
                                    AdtS = AdtS + 1
                                Case "T"
                                    AdtT = AdtT + 1
                                Case Else
                                    AdtO = AdtO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(cw3) > 0 Then
                        If cw3 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtA = AdtA + 1
                                Case "S"
                                    AdtS = AdtS + 1
                                Case "T"
                                    AdtT = AdtT + 1
                                Case Else
                                    AdtO = AdtO + 1
                            End Select
                        End If
                    End If
                    
                    ' Exams
                    If Len(ex1) > 0 Then
                        If ex1 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtAE = AdtAE + 1
                                Case "S"
                                    AdtSE = AdtSE + 1
                                Case "T"
                                    AdtTE = AdtTE + 1
                                Case Else
                                    AdtOE = AdtOE + 1
                            End Select
                        End If
                    End If
                    
                    If Len(ex2) > 0 Then
                        If ex2 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtAE = AdtAE + 1
                                Case "S"
                                    AdtSE = AdtSE + 1
                                Case "T"
                                    AdtTE = AdtTE + 1
                                Case Else
                                    AdtOE = AdtOE + 1
                            End Select
                        End If
                    End If
                    
                    If Len(ex3) > 0 Then
                        If ex3 < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtAE = AdtAE + 1
                                Case "S"
                                    AdtSE = AdtSE + 1
                                Case "T"
                                    AdtTE = AdtTE + 1
                                Case Else
                                    AdtOE = AdtOE + 1
                            End Select
                        End If
                    End If
            
            Case "EHS"
                    If Len(cw1) > 0 Then
                        If cw1 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsA = EhsA + 1
                                Case "S"
                                    EhsS = EhsS + 1
                                Case "T"
                                    EhsT = EhsT + 1
                                Case Else
                                    EhsO = EhsO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(cw2) > 0 Then
                        If cw2 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsA = EhsA + 1
                                Case "S"
                                    EhsS = EhsS + 1
                                Case "T"
                                    EhsT = EhsT + 1
                                Case Else
                                    EhsO = EhsO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(cw3) > 0 Then
                        If cw3 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsA = EhsA + 1
                                Case "S"
                                    EhsS = EhsS + 1
                                Case "T"
                                    EhsT = EhsT + 1
                                Case Else
                                    EhsO = EhsO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(ex1) > 0 Then
                        If ex1 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsAE = EhsAE + 1
                                Case "S"
                                    EhsSE = EhsSE + 1
                                Case "T"
                                    EhsTE = EhsTE + 1
                                Case Else
                                    EhsOE = EhsOE + 1
                            End Select
                        End If
                    End If
                
                    If Len(ex2) > 0 Then
                        If ex2 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsAE = EhsAE + 1
                                Case "S"
                                    EhsSE = EhsSE + 1
                                Case "T"
                                    EhsTE = EhsTE + 1
                                Case Else
                                    EhsOE = EhsOE + 1
                            End Select
                        End If
                    End If
                    
                    If Len(ex3) > 0 Then
                        If ex3 < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsAE = EhsAE + 1
                                Case "S"
                                    EhsSE = EhsSE + 1
                                Case "T"
                                    EhsTE = EhsTE + 1
                                Case Else
                                    EhsOE = EhsOE + 1
                            End Select
                        End If
                    End If
                    
            Case "BCL"
            
            Case "UDB"
            
            Case "UDC"
            
            Case "UDOL"
        End Select
    Next
End Sub

As you can see alot of code is repeated which is one thing i hate having to do, so any advice would be appreciated. The data for this report is sent to me so i am working on it the best i can just trying to reduce the amount of manual work on it.

Many Thanks

J.

 

[SkipVought]

If you ignore previously posted suggestions (you got 2 different valid options), why should anyone take the time to make other suggestions?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

also, if you care to take a wack at using one of these suggestions and posting back code for further help, please declare ALL variables. (You ought to be using Option Explicit as a matter of good practice!)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I would also suggest using [tt]Option Explicit[/tt] at the top of your code.

Have fun.

---- Andy

 

[strongm]

Er ... as PHV says, this could all be simplified by using arrays. Is there some reason that you cannot use them? Here's your code quickly modified to use a couple of arrays. As you can see it dramatically reduces the amount of code. But it is only a start - it looks to me like you could actually reduce this whole thing to only a few lines of code:

[blue]Sub FindData2()

' Faculty Autumn
Dim AdtA As Integer: Dim EhsA As Integer: Dim BclA As Integer
Dim UdbA As Integer: Dim UdcA As Integer: Dim UdolA As Integer

' Faculty Thru
Dim AdtT As Integer: Dim EhsT As Integer: Dim BclT As Integer
Dim UdbT As Integer: Dim UdcT As Integer: Dim UdolT As Integer

' Faculty Spring
Dim AdtS As Integer: Dim EhsS As Integer: Dim BclS As Integer
Dim UdbS As Integer: Dim UdcS As Integer: Dim UdolS As Integer

' Faculty Other
Dim AdtO As Integer: Dim EhsO As Integer: Dim BclO As Integer
Dim UdbO As Integer: Dim UdcO As Integer: Dim UdolO As Integer

' Faculty Autumn
Dim AdtAE As Integer: Dim EhsAE As Integer: Dim BclAE As Integer
Dim UdbAE As Integer: Dim UdcAE As Integer: Dim UdolAE As Integer

' Faculty Thru
Dim AdtTE As Integer: Dim EhsTE As Integer: Dim BclTE As Integer
Dim UdbTE As Integer: Dim UdcTE As Integer: Dim UdolTE As Integer

' Faculty Spring
Dim AdtSE As Integer: Dim EhsSE As Integer: Dim BclSE As Integer
Dim UdbSE As Integer: Dim UdcSE As Integer: Dim UdolSE As Integer

' Faculty Other
Dim AdtOE As Integer: Dim EhsOE As Integer: Dim BclOE As Integer
Dim UdbOE As Integer: Dim UdcOE As Integer: Dim UdolOE As Integer

Dim cw(1 To 3) As String
Dim ex(1 To 3) As String

' Totals
Dim AdtTotal As Integer: Dim EhsTotal As Integer
Dim BclTotal As Integer: Dim UdbTotal As Integer
Dim UdcTotal As Integer: Dim UdolTotal As Integer

Dim lp as long

Dim Faculty As String

    For Each rdata In ThisWorkbook.Sheets("Sheet1").Range("A2:A7502")
        
        rdata.Rows.EntireRow.Select
        currentrow = ActiveCell.Row
        
        Faculty = ThisWorkbook.Sheets("Sheet1").Range("T" & currentrow)
        semester = ThisWorkbook.Sheets("Sheet1").Range("E" & currentrow)
        
        cw(1) = ThisWorkbook.Sheets("Sheet1").Range("F" & currentrow)
        cw(2) = ThisWorkbook.Sheets("Sheet1").Range("H" & currentrow)
        cw(3) = ThisWorkbook.Sheets("Sheet1").Range("J" & currentrow)
        ex(1) = ThisWorkbook.Sheets("Sheet1").Range("L" & currentrow)
        ex(2) = ThisWorkbook.Sheets("Sheet1").Range("N" & currentrow)
        ex(3) = ThisWorkbook.Sheets("Sheet1").Range("P" & currentrow)
        
        Select Case Faculty
            Case "ADT"
                For lp = 1 To 3
                'Coursework
                    If Len(cw(lp)) > 0 Then
                        If cw(lp) < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtA = AdtA + 1
                                Case "S"
                                    AdtS = AdtS + 1
                                Case "T"
                                    AdtT = AdtT + 1
                                Case Else
                                    AdtO = AdtO + 1
                            End Select
                        End If
                    End If
                    
                    ' Exams
                    If Len(ex(lp)) > 0 Then
                        If ex(lp) < 20 Then
                            Select Case semester
                                Case "A"
                                    AdtAE = AdtAE + 1
                                Case "S"
                                    AdtSE = AdtSE + 1
                                Case "T"
                                    AdtTE = AdtTE + 1
                                Case Else
                                    AdtOE = AdtOE + 1
                            End Select
                        End If
                    End If
                Next
            
            Case "EHS"
                For lp = 1 To 3
                    If Len(cw(lp)) > 0 Then
                        If cw(lp) < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsA = EhsA + 1
                                Case "S"
                                    EhsS = EhsS + 1
                                Case "T"
                                    EhsT = EhsT + 1
                                Case Else
                                    EhsO = EhsO + 1
                            End Select
                        End If
                    End If
                    
                    If Len(ex(lp)) > 0 Then
                        If ex(lp) < 20 Then
                            Select Case semester
                                Case "A"
                                    EhsAE = EhsAE + 1
                                Case "S"
                                    EhsSE = EhsSE + 1
                                Case "T"
                                    EhsTE = EhsTE + 1
                                Case Else
                                    EhsOE = EhsOE + 1
                            End Select
                        End If
                    End If
                    
                Next
                                 
            Case "BCL"
            
            Case "UDB"
            
            Case "UDC"
            
            Case "UDOL"
        End Select
    Next
End Sub[/blue]

There are solutions that will allow you to refer to a variable by its string name, but they all tend to be slightly convoluted (since VB provides no native way of doing this)

 

[strongm]

It's been a quiet lunchtime so, here's an example of a short version:

[blue]Sub FindData()
    Dim CourseInfo As New Dictionary ' requires reference to Microsoft Scripting Runtime library to be added to project
    
    Dim cw(1 To 3) As Variant 'String
    Dim ex(1 To 3) As Variant 'String
    
    ' Totals (not used in this sub)
    'Dim AdtTotal As Integer: Dim EhsTotal As Integer
    'Dim BclTotal As Integer: Dim UdbTotal As Integer
    'Dim UdcTotal As Integer: Dim UdolTotal As Integer
    
    Dim Faculty As String
    
    For Each rdata In ThisWorkbook.Sheets("Sheet1").Range("A2:A7502")
        
        Faculty = rdata.Range("T1") 
        semester = rdata.Range("E1") 
        
        cw(1) = rdata.Range("F1") 
        cw(2) = rdata.Range("H1") 
        cw(3) = rdata.Range("J1") 
        ex(1) = rdata.Range("L1") 
        ex(2) = rdata.Range("N1") 
        ex(3) = rdata.Range("P1") 
        
    
        For lp = 1 To 3
        
            'Coursework
            If Len(cw(lp)) > 0 And cw(lp) < 20 Then
                CourseInfo(Faculty & semester) = CourseInfo(Faculty & semester) + 1
            End If
            
            ' Exams
            If Len(ex(lp)) > 0 And ex(lp) < 20 Then
                CourseInfo(Faculty & semester & "E") = CourseInfo(Faculty & semester & "E") + 1
            End If
            
        Next
    Next
                
    ' Just to demonstrate results
    For lp = 0 To CourseInfo.Count - 1
        Debug.Print CourseInfo.Keys(lp), CourseInfo.Items(lp)
    Next

End Sub[/blue]

 

[JasonEnsor]

Hi Guys,

I wasn't ignoring previous suggestions, the code i posted was the full code i had previously created. I had tried to modify it which was the small sample i originally posted. I posted the full version so that anyone who had suggestions would see the full scope of what i was trying to achieve.

I usualy have Option Explicit set by default, but using a new PC and rushing more than i should have to get a solution sorted meant i failed to put it in. I have now set this up again as my default

As for the solution to the problem, it was purely a moment of stupidty as to why i didn't use arrays. Once it was suggested by PHV i started trying to put something together that was way more complex than was needed, i think this was just a case of over complicating what should have been a simple macro. Strongm your 1st solution is simple and would have surficed but then looking at your second attempt it looks fantastic. I have never used 'Dictionary' before so not only will your solution help speed up this code but it gives me some extra studying to do.

Thank you guys for the continued support. I honestly do feel i am getting better at VBA because of the help from members on this site...even if some of my questions can be frustrating and simple.


J.