Help Converting Multi Condition Sum(IF(...)) Array Formula Into VBA Syntax

[MercuryVBA]

Hello,

On the attached data workbook there are two sheets. "Summary" and "Report".

Starting with row 2, column D I've created a multi criteria sum(if()) function that does the following:

On Cell D2 I sum up all the hours on the "Report" sheet where there is a match for B2, C2 and D1 from the "Summary" sheet.

Cell D2 Formula = {=SUM(IF((Report!$B$2:$B$13=Summary!$B2)*(Report!$C$2:$C$13=$C2)*(Report!$D$2:$D$13=D$1),Report!$E$2:$E$13,0))}

I would like to convert this formula to VBA for my macro to perform the calculation without inserting the formula into the cells. The macro should just return the results to the cells.

I tried this in VBA using .Application.WorksheetFunction.SumProduct() - but could not get it to work.

Any help would be much appreciated. Thank you!

"Summary" Sheet

A B C D E 1 Name Serial # Classificaiton 8/8/2014 8/15/2014 2 John 12345 PT 10 7 3 Jack 23456 FT 12 8 4 Christie 34567 PT 1 7 5 Charlie 45678 FT 3 8 <colgroup><col><col span="2"><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

"Report" Sheet

	A	B	C	D	E
1	Name	Serial #	Classificaiton	Date	Hrs
2	John	12345	PT	8/8/2014	4
3	Jack	23456	FT	8/8/2014	4
4	Christie	34567	PT	8/8/2014	1
5	Charlie	45678	FT	8/8/2014	3
6	John	12345	PT	8/8/2014	6
7	Jack	23456	FT	8/8/2014	8
8	Christie	34567	PT	8/15/2014	4
9	Charlie	45678	FT	8/15/2014	2
10	John	12345	PT	8/15/2014	7
11	Jack	23456	FT	8/15/2014	8
12	Christie	34567	PT	8/15/2014	3
13	Charlie	45678	FT	8/15/2014	6

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[MercuryVBA]

Hello WarPiglet the Do While loop keeps crazing my Excel. I not sure why, but when I modify what you've provided to my requirements, it takes for ever to run through the loop and inevitably requires a forced shut down of Excel.

 

[WarPigl3t]

Send me your file.
RA.Ledesma@yahoo.com
Let me try it.

 

[MercuryVBA]

Hello WarPiglet the Do While loop keeps crazing my Excel. I not sure why, but when I modify what you've provided to my requirements, it takes for ever to run through the loop and inevitably requires a forced shut down of Excel.

Also WarPiglet - I have a dynamic number of columns with date headers - hence the reason for structuring the code the way that I did in my post #9 where I dynamically increment the date column using "NextCol" ... I can't list the column ranges individually to write the value as you are suggesting in the following lines of code:

        Sheets("Summary").Range("D" & d).Value = s         Sheets("Summary").Range("E" & d).Value = e</pre>

 

[WarPigl3t]

This was my first time using dynamic columns. Thanks for helping me learn. Here is the working code.

Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 0
                r = 2
                Do Until Sheets(Sht2).Range("B" & r).Value = ""
                    If Sheets(Sht2).Range("B" & r).Value = Sheets(Sht1).Range("B" & d).Value Then
                        If Sheets(Sht2).Range("C" & r).Value = Sheets(Sht1).Range("C" & d).Value Then
                            If Sheets(Sht2).Range("D" & r).Value = Sheets(Sht1).Range(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            col = Chr(col_i + 64)
        Loop
    End If
End Sub

 

[WarPigl3t]

and actually you can delete this if statement from the code.

If Sheets(Sht1).Range("D1").Value <> "" Then

I believe it is already covered in this code anyway.

Do Until Sheets(Sht1).Range(col & "1").Value = ""

 

[MercuryVBA]

This was my first time using dynamic columns. Thanks for helping me learn. Here is the working code.

Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 0
                r = 2
                Do Until Sheets(Sht2).Range("B" & r).Value = ""
                    If Sheets(Sht2).Range("B" & r).Value = Sheets(Sht1).Range("B" & d).Value Then
                        If Sheets(Sht2).Range("C" & r).Value = Sheets(Sht1).Range("C" & d).Value Then
                            If Sheets(Sht2).Range("D" & r).Value = Sheets(Sht1).Range(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            col = Chr(col_i + 64)
        Loop
    End If
End Sub

WarPiglet, Thank you very much for spending so much time to help me solve my problem thus far. I sincerely appreciate it it. However, although the solution you presented in post #14 works on the sample test file that I sent to you, it won't work on my actual. For example, in my actual "Summary" / data file, I have columns that exceed range "Z" and go into cols AA - ZA - if you use chr (col_i + 64) I believe we are limited to "Z" as the last column, then we'll get crazy headers from the ASCII table.

Honestly - I really need a solution to resolve the code I presented in post #9 - Nonetheless, I am glad we both learned something from each other through this positive interaction Thanks again!

P.S. I'll give your solution another try to see if I can mold it into my code.

 

[MercuryVBA]

War Piglet - I've been able to resolve my issue using my code in post #9 - however, looking at your method with the Do While is what gave me the idea to develop it the way that I did to solve my issue - Thank you again for your assistance.

The core of the code is in the If Then Else - I had issues in other areas that were returning incorrect results ... I'm good to go now. Have a good one!

 

[WarPigl3t]

Awesome. I wasn't going to have time to work on your issue for another 2 days so I'm glad you got it working. Those do until loops are awesome.

 

[WarPigl3t]

Turns out I had some time after all. I know you already had a solution. But here is my solution. I tested it. It can go all the way to ZZ.

Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col_d = 0
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 0
                r = 2
                Do Until Sheets(Sht2).Range("B" & r).Value = ""
                    If Sheets(Sht2).Range("B" & r).Value = Sheets(Sht1).Range("B" & d).Value Then
                        If Sheets(Sht2).Range("C" & r).Value = Sheets(Sht1).Range("C" & d).Value Then
                            If Sheets(Sht2).Range("D" & r).Value = Sheets(Sht1).Range(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            If col_i > 26 Then
                col_i = 1
                col_d = col_d + 1
            Else
            End If
            If col_d > 0 Then
                col = Chr(col_d + 64) & _
                Chr(col_i + 64)
            Else
                col = Chr(col_i + 64)
            End If
        Loop
    End If
End Sub