VBA Populating matrix with 3 criteria


Board Regular
Oct 31, 2011
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).


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!

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sub DisciplineStatus()
    Dim varEstatus() As Variant
    Dim varIstatus() As Variant
    Dim iWeek As Integer
    Selection.Offset(1, 0).Activate
    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 :p
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back