# Using Looping Through MyArray using SumProduct Formula

0

Hi this is part 2 of my COUNTSAMPLE file.  I have created an array.  My goal is to loop through the array and place the SumProduct formula which does a count of CC From, CC To, CORRECTED. Column N, O, P reflects how the data should appear.  It works for the first row only.  I missing a counter of some sort. The Code is on the a separte tab. Can you help?

Sub CountCfCtCo()

Dim vs As Worksheet

Set vs = Worksheets("SAMPLED DATA")

Dim Rng As Range

Dim Eclm As Long

Dim erow As Long

Dim i As Long

i = 8

Dim mYaRRay As Variant

Dim Eid As Long

Eid = vs.Cells(i, 2)

Dim ExpD As Long

ExpD = vs.Cells(i, 6)

Dim erow2 As Long

Dim cnt As Long

With vs

erow = .Cells(.Rows.Count, 1).End(xlUp).Row

Eclm = .Cells(7, .Columns.Count).End(xlUp).Column

Set Rng = .Range(.Cells(8, "B"), .Cells(erow, Eclm))

End With

mYaRRay = Rng.Value

Debug.Print mYaRRay(1, 1), mYaRRay(2, 1), mYaRRay(2, 2), mYaRRay(2, 3), mYaRRay(2, 4)

For Each cell In mYaRRay

If vs.Cells(i, 2) <> vs.Cells(i + 1, 2) And vs.Cells(i, 6) <> vs.Cells(i + 1, 6) Then

vs.Range(i, 14).Formula = " "

vs.Range(i, 15).Formula = " "

vs.Range(i, 16).Formula = " "

cnt = cnt + 1

End If

If vs.Cells(i, 2) = vs.Cells(i + 1, 2) And vs.Cells(i, 6) <> vs.Cells(i + 1, 6) Then

vs.Range(i, 14).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$3))"

vs.Range(i, 15).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$4))"

vs.Range(i, 16).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$5))"

cnt = cnt + 1

End If

If vs.Cells(i, 2) = vs.Cells(i + 1, 2) And vs.Cells(i, 6) = vs.Cells(i + 1, 6) Then

vs.Cells(i, 14).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$3))"

vs.Cells(i, 15).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$4))"

vs.Cells(i, 16).Formula = "=SUMPRODUCT((B:B=\$D8)*(F:F=\$G8)*(E:E=\$E\$5))"

cnt = cnt + 1

End If

Next

End Sub

### Discussion

Please always enclose the code you post between Code tags. You can einer press "CODE" first and replace "Code_Goes_Here" with the code you wish to publish or paste the code first, select it, and then press "CODE".
Variatus (rep: 3738) Feb 22, '19 at 10:11 pm
Okay, now I see.  Sorry about that. I hadn't noticed the availabitly to do so. Thanks for opening my eyes.
CoraG (rep: 6) Feb 25, '19 at 12:27 pm

0

Hi Cora,

What's the point in using VBA to write a worksheetfunction into a cell so that the function might do the work? Why not let VBA do the work? You will find the code below working in the attached workbook. Look for it in the TeachExcel module.

``````Sub WriteCount()
' 23 Feb 2019

Const Eid As String = "B"               ' adjust as required
Const FirstDataRow As Long = 8

Dim Crit() As String, i As Integer
Dim Rng As Range
Dim Rl As Long
Dim C As Long
Dim R As Long

Crit = Split("CC From,CC To,CORRECTED", ",")
With Worksheets("Sampled Data")         ' change as required
' don't work with ranges like "B:B" or "E:E"
' instead, define the range you are interested in:-
Rl = .Cells(.Rows.Count, Eid).End(xlUp).Row
Set Rng = .Range(.Cells(FirstDataRow, Eid), .Cells(Rl, Eid))

For R = FirstDataRow To Rl
C = Columns("N").Column         ' first result column
For i = 0 To UBound(Crit)       ' Crit has 3 elements: 0 to 2
.Cells(R, C).Value = CountFromTo(R, Crit(i), Rng)
C = C + 1
Next i
Next R
End With
End Sub

Private Function CountFromTo(ByVal R As Long, _
ByVal Crit As Variant, _
Rng As Range) As Integer
' 23 Feb 2019

Const ClmE As Long = 1
Const ClmF As Long = 2

Dim Ws As Worksheet
Dim Tgt(1 To 2) As Range

With Rng
Set Ws = .Worksheet
Set Tgt(ClmE) = .Offset(0, 3)        ' 3 defines column E
Set Tgt(ClmF) = .Offset(0, 4)        ' 4 defines column F
End With

CountFromTo = Application.WorksheetFunction.CountIfs( _
Tgt(ClmE), Crit, _
Rng, Ws.Cells(R, Rng.Column).Value, _
Tgt(ClmF), Ws.Cells(R, Tgt(ClmF).Column).Value)
End Function
``````

### Discussion

Hi, I agree.  Like I said, I am still learning this language and I would love to use funcitons more than subroutines for all of the vba processing I am needing. I need to look more at understanding it since almost off the analysis' I do require outputs.
CoraG (rep: 6) Feb 25, '19 at 12:31 pm
Hello and thanks for the help.  I do have a question.  If you look at the spreadsheet I provided you with an example of how the end result should look.  So, the problem I am having is that the data fills in each row.

The criteria is per Eid, per Date do a count. For example Eid 1234 has two changes he made to his time card.  ExpE(ColF) shows his transactions reflect the following:
10/10 = CC From (1) & CC To (3) CORRECTED (0)
10/11 = CC From(2) & CC To (2) CORRECTED (0)
How do I get the total on the line where the date changes?  I want everything between to be blank as reflected in the spreadsheet.  I tried the code on the original spreadsheet and made the appropriate changes reflecting the appropriate columns/ranges etc, but it is putting in zeros.  Even converting Ein to numbers did not change it.  Let me know your thoughts.
CoraG (rep: 6) Feb 26, '19 at 10:46 am
Sorry Cora, I thought about this for 2 days (while following a busy travelling schedule on the side) and seem to make no headway. I fail to follow your example and therefore focus on the one sentence that is clear to me, to wit, "How do I get the total on the line where the date changes?" I can't figure out what "the total" is, and, of course, "the line where the date changes" can't, logically, be a single row because the change itself is documented in two rows, like rows 11:12. In row 12 the count is 2, 1 and nothing. You seem to say it's 2, 2 and 0, but it's neither clear that we are looking at the same data, nor which of these data, if any, reflect "the total" you want, nor how the data I'm looking at where created (by my code or your manual input).
Variatus (rep: 3738) Feb 27, '19 at 9:30 pm