我有一份原始数据表，正在尝试处理急诊科(ANE(与其他所有部门的nps评分。
我正在计算价值，并将发起人、诋毁者和被动语态的数量转移到一张表上，然后我可以计算当月的净发起人得分。
我有四个研究所正在接受调查，而且是这样运作的。
我收到
编译下一个错误，没有for。
是哪个End If导致错误，还是我丢失了更多的End If？
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
所有这些变量迟早会让你头疼：维护一种更灵活的方法会更容易，比如下面，使用字典来收集计数，使用你感兴趣的值的组合来键入。
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