Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

        Debug.Print Rng.Address

               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

excelmacros-vba
MS Office Version: 2013
posted by: CoraG (rep: 6) on Fri Feb 22, 2019 at 5:01 pm
Answer
Discuss

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: 4889) 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
Add to Discussion

Answers

0
Selected Answer

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
posted by: Variatus (rep: 4889) on Fri Feb 22, 2019 at 11:23 pm
Discuss

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: 4889) Feb 27, '19 at 9:30 pm
Much as I want to help, I feel that the sheer volume of information that would have to be provided just to push me to the starting line by far exceeds the limits set by the format of this forum's Discussion section, and if we do manage to explain your problem and if we then do find a solution there is nowhere in this forum's format where an answer could be suitably presented. In this thread you asked (Q) how to write a Sumproduct() function to a worksheet using VBA. I suggested (A) to use VBA to write the result instead. You should either accept or reject (since it wasn't what you asked) my suggestion. Either way is fair but there is no avoiding the fact that the thread has come to an end by virtue of both Q & A existing in it. By creating a new question you could invite other talent here to take up your cause and improve the quality of help you get or - since I read all questions anyway - give yourself the opportunity to rephrase your question and better explain yourself in the light of the progress, if any, you could make as a result of this thread.
Variatus (rep: 4889) Feb 27, '19 at 9:30 pm
Hi there! No problem, I understand and I think I got this now.  I do accept your answer and this is precisely where I want to go in terms of doing all that I can do in a function rather than a subroutine. I didn't see a button for accepting the answer but I want you to know that I am very pleased and happy that you were able help me with the solution.  I do have one minor question regarding the function that has the the Tgt (1 to 2).  Is it 1 to 2 becasue the two columns happen to be close to eachother?  If there were columns in between would it still be 1 to 2? Thank you.
CoraG (rep: 6) Mar 1, '19 at 2:14 pm
Thanks for the assurance, Cora. Look for a fat, blue button with white caption "Select Answer" at the bottom of the answer. Click it.
Yes, it's always 1 and 2. The columns can be anywhere on the sheet. They are defined further down as ".Offset(0, 3)        ' 3 defines column E"
Variatus (rep: 4889) Mar 1, '19 at 8:46 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login