'Sorting responses into categories

I have a raw data sheet and am trying to process nps scores from the emergency department (ANE) versus all others.

I am counting the values and transferring the number of promoters, detractors and passives onto a sheet which I can then calculate the net promoter score for that month.

I have four institutes which are being surveyed and it's run as such.

I receive

Compile error next without for.

Which End If is causing the error, or am I missing more End Ifs?

Dim UNITAOutpatientANEPromoter As Integer
Dim UNITBOutpatientANEPromoter As Integer
Dim UNITCOutpatientANEPromoter As Integer
Dim UNITDOutpatientANEPromoter As Integer
Dim ALLOutpatientANEPromoter As Integer

Dim UNITAOutpatientANEDetractor As Integer
Dim UNITBOutpatientANEDetractor As Integer
Dim UNITCOutpatientANEDetractor As Integer
Dim UNITDOutpatientANEDetractor As Integer
Dim ALLOutpatientANEDetractor As Integer

Dim UNITAOutpatientANEPassive As Integer
Dim UNITBOutpatientANEPassive As Integer
Dim UNITCOutpatientANEPassive As Integer
Dim UNITDOutpatientANEPassive As Integer
Dim ALLOutpatientANEPassive As Integer

Dim UNITAOutpatientOtherPromoter As Integer
Dim UNITBOutpatientOtherPromoter As Integer
Dim UNITCOutpatientOtherPromoter As Integer
Dim UNITDOutpatientOtherPromoter As Integer
Dim ALLOutpatientOtherPromoter As Integer

Dim UNITAOutpatientOtherDetractor As Integer
Dim UNITBOutpatientOtherDetractor As Integer
Dim UNITCOutpatientOtherDetractor As Integer
Dim UNITDOutpatientOtherDetractor As Integer
Dim ALLOutpatientOtherDetractor As Integer

Dim UNITAOutpatientOtherPassive As Integer
Dim UNITBOutpatientOtherPassive As Integer
Dim UNITCOutpatientOtherPassive As Integer
Dim UNITDOutpatientOtherPassive As Integer
Dim ALLOutpatientOtherPassive As Integer

UNITAOutpatientANEPromoter = 0
UNITBOutpatientANEPromoter = 0
UNITCOutpatientANEPromoter = 0
UNITDOutpatientANEPromoter = 0
ALLOutpatientANEPromoter = 0

UNITAOutpatientANEDetractor = 0
UNITBOutpatientANEDetractor = 0
UNITCOutpatientANEDetractor = 0
UNITDOutpatientANEDetractor = 0
ALLOutpatientANEDetractor = 0

UNITAOutpatientANEPassive = 0
UNITBOutpatientANEPassive = 0
UNITCOutpatientANEPassive = 0
UNITDOutpatientANEPassive = 0
ALLOutpatientANEPassive = 0

UNITAOutpatientOtherPromoter = 0
UNITBOutpatientOtherPromoter = 0
UNITCOutpatientOtherPromoter = 0
UNITDOutpatientOtherPromoter = 0
ALLOutpatientOtherPromoter = 0

UNITAOutpatientOtherDetractor = 0
UNITBOutpatientOtherDetractor = 0
UNITCOutpatientOtherDetractor = 0
UNITDOutpatientOtherDetractor = 0
ALLOutpatientOtherDetractor = 0

UNITAOutpatientOtherPassive = 0
UNITBOutpatientOtherPassive = 0
UNITCOutpatientOtherPassive = 0
UNITDOutpatientOtherPassive = 0
ALLOutpatientOtherPassive = 0


Dim mycount As Integer
    mycount = Worksheets("1. Raw").Range("A1", Worksheets("1. Raw").Range("A1").End(xlDown)).Rows.Count - 1
    'MsgBox (mycount)
    If (mycount = 0 Or mycount = 1) Then
        MsgBox ("Need raw data first")
    
    Else 'mycount > 1
        Dim i As Integer
        'MsgBox (mycount)
        For i = 1 To mycount
      

           
            If (Worksheets("1. Raw").Cells(i + 1, 1)) = "OUTPATIENT" Then
                'UNITA
                If (Worksheets("1. Raw").Cells(i + 1, 2)) = "UNITA" Then
                    If (Worksheets("1. Raw").Cells(i + 1, 6)) = "A&E-A" Then
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITAOutpatientANEPromoter = UNITAOutpatientANEPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITAOutpatientANEDetractor = UNITAOutpatientANEDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITAOutpatientANEPassive = UNITAOutpatientANEPassive + 1
                        End If
            
                Else
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITAOutpatientOtherPromoter = UNITAOutpatientOtherPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITAOutpatientOtherDetractor = UNITAOutpatientOtherDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITAOutpatientOtherPassive = UNITAOutpatientOtherPassive + 1
                        End If
                    End If
                

                 'UNITB
                ElseIf (Worksheets("1. Raw").Cells(i + 1, 1)) = "UNITB" Then
                    If (Worksheets("1. Raw").Cells(i + 1, 6)) = "A&E-B" Then
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITBOutpatientANEPromoter = UNITBOutpatientANEPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITBOutpatientANEDetractor = UNITBOutpatientANEDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITBOutpatientANEPassive = UNITBOutpatientANEPassive + 1
                        End If
            
                    Else
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITBOutpatientOtherPromoter = UNITBOutpatientOtherPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITBOutpatientOtherDetractor = UNITBOutpatientOtherDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITBOutpatientOtherPassive = UNITBOutpatientOtherPassive + 1
                        End If
                    End If
                 

                'UNITC
                ElseIf (Worksheets("1. Raw").Cells(i + 1, 1)) = "UNITC" Then
                    If (Worksheets("1. Raw").Cells(i + 1, 6)) = "A&E-C" Then
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITCOutpatientANEPromoter = UNITCOutpatientANEPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITCOutpatientANEDetractor = UNITCOutpatientANEDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITCOutpatientANEPassive = UNITCOutpatientANEPassive + 1
                        End If
                    
                    Else
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITCOutpatientOtherPromoter = UNITCOutpatientOtherPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITCOutpatientOtherDetractor = UNITCOutpatientOtherDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITCOutpatientOtherPassive = UNITCOutpatientOtherPassive + 1
                        End If
                    End If
             

                'UNITD
                Else
                If (Worksheets("1. Raw").Cells(i + 1, 1)) = "UNITD" Then
                    If (Worksheets("1. Raw").Cells(i + 1, 6)) = "A&E-D" Then
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITDOutpatientANEPromoter = UNITDOutpatientANEPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITDOutpatientANEDetractor = UNITDOutpatientANEDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITDOutpatientANEPassive = UNITDOutpatientANEPassive + 1
                        End If
            
                    Else
                        If (Worksheets("1. Raw").Cells(i + 1, 22)) = "Promoter" Then
                        UNITDOutpatientOtherPromoter = UNITDOutpatientOtherPromoter + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Detractor" Then
                        UNITDOutpatientOtherDetractor = UNITDOutpatientOtherDetractor + 1
                        ElseIf (Worksheets("1. Raw").Cells(i + 1, 22)) = "Passive" Then
                        UNITDOutpatientOtherPassive = UNITDOutpatientOtherPassive + 1
                        End If
                    End If
             End If
             
        Next i

    End If

excel vba

Solution 1:^[1]

All of those variables will give you a headache sooner or later: it would be easier to maintain a more-flexible approach such as below, using a Dictionary to collect the counts, keyed using a combination of the values you're interested in.

Sub Tester()

    Dim wsRaw As Worksheet
    Dim i As Integer, valUnit, valType, AandE As String, dict As Object, k, lastRow As Long
    
    Set dict = CreateObject("scripting.dictionary")
    
    Set wsRaw = Worksheets("1. Raw")
    lastRow = wsRaw.Cells(Rows.Count, "A").End(xlUp).Row
    
    'MsgBox (mycount)
    If lastRow < 2 Then
        MsgBox ("Need raw data first")
        Exit Sub
    End If
    
    For i = 2 To lastRow
        If wsRaw.Cells(i, "A") = "OUTPATIENT" Then
            valUnit = Trim(wsRaw.Cells(i, "B").Value)
            valType = Trim(wsRaw.Cells(i, 22).Value)
            AandE = IIf(Trim(wsRaw.Cells(i, 6).Value) = "A&E-A", "A&E", "NonA&E") 'is this A&E ?
            
            Select Case valUnit
                Case "UNITA", "UNITB", "UNITC", "UNITD"                 'counting this unit?
                    Select Case valType
                        Case "Promoter", "Detractor", "Passive"         'counting this type?
                            k = valUnit & "-" & valType & "-" & AandE 'create key for dictionary
                            dict(k) = dict(k) + 1                       'increment count for key
                    End Select
            End Select
        End If 'outpatient
    Next i
    
    'output all the collected keys and counts
    For Each k In dict
        Debug.Print k, dict(k)
    Next k
    
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Tim Williams

'Sorting responses into categories

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]