Earlier I asked a question where I couldn't get a correct answer. I think I couldn't express myself properly. This is a problem I am facing:
Sub organize()
Dim GroupID(1 To 30) As String
Dim GroupAanwezig As Long
Dim class As String
Dim i, j, t, totally, countCol As Integer
Dim GroupenCol As New Collection
i = 1
t = 1
rn_totallist = Worksheets("totallist").Cells.SpecialCells(xlCellTypeLastCell).Row
nxA_9N4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_9N4")
nxA_1A2A = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1A2A")
nxA_1A2B = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1A2B")
nxA_2A2 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_2A2")
nxA_2A3 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_2A3")
nxA_3A3 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_3A3")
nxA_1B4A = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1B4A")
nxA_1B4B = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1B4B")
nxA_1B4C = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1B4C")
nxA_1B4D = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1B4D")
nxA_2G4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_2G4")
nxA_3G4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_3G4")
nxA_4G4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_4G4")
nxA_2A4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_2A4")
nxA_3A4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_3A4")
nxA_4A4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_4A4")
nxA_1E3 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1E3")
nxA_1E4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_1E4")
nxA_2E4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_2E4")
nxA_3e3 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_3E3")
nxA_3e4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_3E4")
nxA_4e4 = Application.WorksheetFunction.CountIf(Sheets("totallist").Range("G2:G" & rn_totallist), "xA_4E4")
totalstd = nxA_9N4 + nxA_1A2A + nxA_1A2B + nxA_2A2 + nxA_2A3 + nxA_3A3 + nxA_1B4A + nxA_1B4B + nxA_1B4C + nxA_1B4D + nxA_2G4 + nxA_3G4 + nxA_4G4 + nxA_2A4 + nxA_3A4 + nxA_4A4 + nxA_1E3 + nxA_1E4 + nxA_2E4 + nxA_3e3 + nxA_3e4 + nxA_4e4
GroupenCol.Add nxA_9N4
GroupenCol.Add nxA_1A2A
GroupenCol.Add nxA_1A2B
GroupenCol.Add nxA_2A2
GroupenCol.Add nxA_2A3
GroupenCol.Add nxA_3A3
GroupenCol.Add nxA_1B4A
GroupenCol.Add nxA_1B4B
GroupenCol.Add nxA_1B4C
GroupenCol.Add nxA_1B4D
GroupenCol.Add nxA_2G4
GroupenCol.Add nxA_3G4
GroupenCol.Add nxA_4G4
GroupenCol.Add nxA_2A4
GroupenCol.Add nxA_3A4
GroupenCol.Add nxA_4A4
GroupenCol.Add nxA_1E3
GroupenCol.Add nxA_1E4
GroupenCol.Add nxA_2E4
GroupenCol.Add nxA_3e3
GroupenCol.Add nxA_3e4
GroupenCol.Add nxA_4e4
LastRow = Sheets("BLS").Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 2 To LastRow
LastCol = Sheets("BLS").Cells(i, Columns.count).End(xlToLeft).Column
For j = 2 To LastCol
class = Sheets("BLS").Cells(i, j).Value
GroupAanwezig = WorksheetFunction.CountIf(Range("B1:D18"), class)
BLSers = Sheets("BLS").Cells(i, 1).Value
If InStr(nshow, class) = 0 Then
GroupID(t) = class
nshow = nshow & " " & GroupAanwezig & " " & t & " " & GroupID(t) & vbCrLf
t = t + 1
End If
countCol = Sheets("BLS").Cells(i, Columns.count).End(xlToLeft).Column
lastcolumn = countCol - 1
Select Case lastcolumn
Case Is = 0
'MsgBox "EMPTY"
Case Is = 1
totally = lastcolumn * 30
Case Is = 2
totally = ((lastcolumn * 10) - 5) * 2
Case Is = 3
totally = lastcolumn * 10
nclass = "n" + class
MsgBox nclass
'---------------------------------------------------------
'there has to be a faster way to do the if-statement below.
'---------------------------------------------------------
If class = "xA_9N4" Then
MsgBox nxA_9N4
End If
If class = "xA_4A4" Then
MsgBox nxA_4A4
End If
If class = "xA_3A4" Then
MsgBox nxA_3A4
End If
If class = "xA_1B4B" Then
MsgBox nxA_1B4B
End If
If class = "xA_1A2B" Then
MsgBox nxA_1A2B
End If
If class = "xA_2A4" Then
MsgBox nxA_2A4
End If
If class = "xA_2E4" Then
MsgBox nxA_2E4
End If
If class = "xA_1E3" Then
MsgBox nxA_1E3
End If
'--------------------------------------------------------
End Select
End Sub
In the last case (case is = 3) there is a if-statement. This if statement is a shor version (this needs to be repeated in case 0, case 1 and case 2). I need to do something with the returned value (for example) nxA_9N4. In an earlier attempt to solve a problem I got help from:
engineersmnky he told me to use a collection.
I used a collection but still :(.
Comment answers: -if a cell.value is has the same value as the class (which is always the case) then I will return the value from another sheet. This value counts the groups/classes called "xA4A" and returns the countvalue as nxA4A. with the ifstatement I would like to know if class = "xA4A" then give me the count of nxA4A.
By the way eval function doesn't work in 2013.
If you look at the Collection.Add method, you'll see it can take up to 4 parameters. The first is the object to be added (as you're doing already) but the second optional parameter is the key - a unique string value used to identify this object in the collection.
So, if you add items into the collection like this:
GroupenCol.Add nxA_9N4, "xA_9N4"
and you have the required string value in a variable called "class", you could then access the collection like this:
MsgBox GroupenCol(class)
or this:
MsgBox GroupenCol.Item(class)
(These are equivalent because Item is the default property of a Collection)