VBA Populating matrix with 3 criteria

[xancalagonx]

Hey guys.

Struggling with some VBA code.

The setup is this...

I have a report with data I export to excel. This report contains tagnumbers for equipment, a status for the tag (OK, blank, PB), the week when status was entered and a disicpline code (E-Electro, I-instrumentation etc).

E.g.

Tag / status / week / discipline
13-FO-9999 / ok / 44 / I
26-FE-8888 / pb / 40 / E
16-TT-7777 / blank / 40 / I

What I want is to transfer this data into a matrix (which will then be used as the data for a graph).

What we want to see is the status for each discipline spread out across the weeks. E.g.

Discipline / w40 / w41 / w42 /
Electro ok / 35 / 75 / 30
Electro pb / 10 / 7 / 30

My knowledge of the various commands in VBA is a bit limited, but I was thinking about using IF, as well as CASE, but seems to me I'd have to make dozens of various IF or CASE bits of code to cover it all.

Considering I have around 11 disciplines, weeks from week 35 this year and will be going into week 40-50 next year, and there are 4 different status for each relevant tagnumber I want to count up (ok, pa, pb and blank).

So if I want to add how many electrical tagnumbers were reported OK in week 40, I have Electro + OK + week 40 to take into account as the criteria before it should count that as 1 status under "Electro ok" for "w40".

Anyone who could help point me in the right direction?

Been trying stuff like 'IF activecell.value = "Electro" AND activecell.offset(-1,0) = "ok" .... but doesn't seem to work.

I was thinking about adding the values into an array as well so I could sum up all the values for week 40 then add that as a varStatusOK(40) = (count of all ok status for a discipline for week 40) then simply insert varStatusOK(40) into the week 40 cell. Then similarily use varStatusOK(41) for week 41 and so on.

Maybe I just need more coffee.

Any help appreciated!

 

[xancalagonx]

Sub DisciplineStatus()
    Dim varEstatus() As Variant
    Dim varIstatus() As Variant
    Dim iWeek As Integer
 
    Range("1:1").Find("Disiplin").Select
    Selection.Offset(1, 0).Activate
 
Do
    Select Case ActiveCell.Value
    Case "E"
        If ActiveCell.Offset(0, -2) = "ok" Then
        iWeek = ActiveCell.Offset(0, -1).Value
        ReDim Preserve varEstatus(iWeek)
        varEstatus(iWeek) = varEstatus(iWeek) + 1
        ActiveCell.Offset(1, 0).Activate
        End If
    End Select
    ActiveCell.Offset(1, 0).Activate
Loop Until ActiveCell.Offset(0, -3) = ""
Range("AD2").Value = varEstatus(43)
Range("AE2").Value = varEstatus(44)
Range("AF2").Value = varIstatus(43)
Range("AG2").Value = varIstatus(44)
Range("AC2").Value = Nostatus
 
End Sub

Here is some of the code I have been using, though not too successfully.

I was hoping to assign the count to the "slot" of the array that would represent the week number.

Probably a bit messy for those that have a better understanding of this than myself

What it's doing is going down the column with the discipline code, like E for Electro... then offsetting to check the status of the status column on same row. Then it sets iWeek to be the week number as listed in the 3rd column, which is the week the tagnumber on that particular row was set to status "ok".

Then I want to add that as 1 count into the varEstatus(iWeek) array to count 1 up for everytime there was an ok for the iWeek value.

Hope this makes sense