VBA Help - Using Join Function to build dynamic Sum Formula - Excel 2016

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
Hello All,


I am working on a project and head a road block with some code, there is a super manual way to do this but I know that this can be done with some form of Join code I am just not that familiar with the best way to approach this.

Here is my problem:

I have a loop that runs down row by row on my lookups sheet for some categories, when a non-blank cell is found, the code offsets two columns over to find a numeric variable which is a count for how many codes the formula will need to process which is defined by the string cCount which can be 1 - 10. Directly below that row I do the same defining of a variable gCount
which can also have the numeric value 1 -10.

an example of what I am doing with the variables below

Scenario 1#
cCount = 0
gCount = 1

Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"

In the example above, cCount was 0 which is why there is no variable for cItem included and only a gItem variable within the formula
*cItem and gItem are the values one column over from the respective Count variables.

Scenario 2#
cCount = 1
gCount = 1

Formula result should look like this: .FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ")*(cData = " & cItem.Value & ") *(IOHeader=RC4)* (Hdata = R7C) * DataTable)/1000"

In the example above, cCount was 1 and gCount was 1 which is why both are included in the formula

Scenario 3# Where things start getting more complex
cCount = 2
gCount = 1

Formula result should look like this: FormulaR1C1 = "=SUMPRODUCT((GData = " & gItem.Value & ") *(IOHeader=RC4)*(CData = " & cItem.Value & ") * (Hdata = R7C) * DataTable)/1000+SUMPRODUCT((GData = " & gItem.Offset(0, 1).Value & ") *(IOHeader=RC4)*(CData = " & cItem.Offset(0, 1).Value & ") * (Hdata = R7C) * DataTable)/1000"

In this example, cCount was 2 which requires that the formula be written 2 times to include both the original cItem value as well as the next value in the adjacent cell, gCount would be repeated in both formulas, in the event that gCount was 2 as well then the formula would need to be written to account for all variations of the cItem and gItem variations.

This scenario could be played out up to 10 values for each cItem and gItem.

So, I am able to achieve the lookups of each scenario using a case statement. I am just applying some math and adding both cCount and gCount together, and processing them that way, the problem that I face is having to write out the formula x amount of times based on the various possibilities of count.

Any direction would be great. Thanks in advance.



FYI - I was able to think of a better way of doing this but I hit a wall with that approach hence this above method.
So using the macro recorder I went to my matrix where cItem and gItem are found and copied each variable and went to my Report data and filtered the contents by each of the variables appending each variable to the filter until all values were met then just summed up the $Amounts and pasted that into the cells where my formulas would have gone. This was fast and easy problem was when I attempted to modify the recorder code and do this dynamically with variables instead of manually selecting everything the code wouldn't work.

My brain is fried at this point.... LOL
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

If I understand correctly, you could simplify the formula.

For ex., for
gCount=3 with the values 1,4,7
cCount=2 with the values 3, 8

you could use the formula:

=SUM(SUMIFS(DataTable,GData,{1,4,7},IOHeader,RC4,CData,{3;8},Hdata = R7C))/1000

This makes the translation to vba much simpler.

Please comment.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
=SUM(SUMIFS(DataTable,GData,{1,4,7},IOHeader,RC4,CData,{3;8},Hdata = R7C))/1000

There was a typo at the end of the formula

=SUM(SUMIFS(DataTable,GData,{1,4,7},IOHeader,RC4,CData,{3;8},Hdata,R7C))/1000
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
Thank you for the response. I am looking into your formula and after manually placing it on my summary sheet and linking the R1C1 references the formula gets a #Value error?

I assume the GData{1,4,7} needs to be updated with whatever values need to be entered for the GData values and the same for CData?

Also, after entering the formula I CTRL+SHOFT+ENTERED since this looks like an array formula.
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

Thank you for the response. I am looking into your formula and after manually placing it on my summary sheet and linking the R1C1 references the formula gets a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] error?

I assume the GData{1,4,7} needs to be updated with whatever values need to be entered for the GData values and the same for CData?

Also, after entering the formula I CTRL+SHOFT+ENTERED since this looks like an array formula.


This is what the revised formula looks like but it results in a #VALUE ERROR - =SUM(SUMIFS(DataTable,GData,{7310000,7860000},IOHeader,D16,Cdata,{1920},Hdata,J7))/1000
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

The formula you posted should work well, unless

- you have error values in your data
or
- some of the data ranges do not have the same dimensions as the other ones

Please clarify

P. S. In the latter case you can do a loop to build the formula using the logic in post #1
I can post the code tonight (GMT)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

This is an example

Assuming

cItem is A2
gItem is A3

this code generates the formula in your post #1

Code:
Sub Test()
Dim cCount As Long, gCount As Long
Dim vArr As Variant, v As Variant
Dim sF As String, sF1 As String, sF2 As String
Dim gItem As Range, cItem As Range

sF1 = "SumProduct (#g# * (IOHeader = RC4) * [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c"]#c[/URL] # * (Hdata = R7C) * DataTable) / 1000"

Set cItem = Range("A2")
Set gItem = Range("A3")
cCount = 2
gCount = 3

vArr = GetPermutations(cCount, gCount)
For Each v In vArr
    sF2 = sF1
    If v(0) = 0 Then sF2 = Replace(sF2, "[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c"]#c[/URL] # *", "") Else sF2 = Replace(sF2, "[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c"]#c[/URL] #", "(CData = " & cItem.Offset(0, v(0) - 1).Value & ")")
    If v(1) = 0 Then sF2 = Replace(sF2, "#g# *", "") Else sF2 = Replace(sF2, "#g#", "(GData = " & gItem.Offset(0, v(1) - 1).Value & ")")
    sF = sF & "+" & sF2
Next v
sF = "=" & Mid(sF, 2)
End Sub

' generates the permutations given cCoutn and gCount
Function GetPermutations(cCount As Long, gCount As Long) As Variant
Dim i As Long, j As Long, k As Long
Dim vArr As Variant

ReDim vArr(1 To IIf(cCount = 0, 1, cCount) * IIf(gCount = 0, 1, gCount))

For j = IIf(cCount = 0, 0, 1) To cCount
    For k = IIf(gCount = 0, 0, 1) To gCount
        i = i + 1
        vArr(i) = VBA.Array(j, k)
    Next k
Next j

GetPermutations = vArr

End Function

For ex., for

cCount=2
gCount=3

I got the result:

Code:
=SumProduct ((GData = 10) * (IOHeader = RC4) * (CData = 1) * (Hdata = R7C) * DataTable) / 1000
+SumProduct ((GData = 20) * (IOHeader = RC4) * (CData = 1) * (Hdata = R7C) * DataTable) / 1000
+SumProduct ((GData = 30) * (IOHeader = RC4) * (CData = 1) * (Hdata = R7C) * DataTable) / 1000
+SumProduct ((GData = 10) * (IOHeader = RC4) * (CData = 2) * (Hdata = R7C) * DataTable) / 1000
+SumProduct ((GData = 20) * (IOHeader = RC4) * (CData = 2) * (Hdata = R7C) * DataTable) / 1000
+SumProduct ((GData = 30) * (IOHeader = RC4) * (CData = 2) * (Hdata = R7C) * DataTable) / 1000

Which seems correct.

Please test for different values of cCount and gCount

This is the data I used in the test:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >I</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >J</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >K</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">7</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">8</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">9</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">10</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">10</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">20</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">30</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">40</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">50</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">60</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">70</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">80</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">90</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">100</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=12 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>
 
Last edited:

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
Thank you for all your help on this. I am trying the code out now.
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
So, the code looks amazing and seems like I will be able to adapt this to my main code to get the results needed.

I am getting a weird error, so I can Debug.Print the results, I can MSGBOX the results but I can drop the results into a cell on my summary page?

I keep getting a Runtime Error: 1004 Application Defined or Object Defined error

Not sure if maybe the R1C1 reference isn't getting picked up which is causing the formula to result in an error and VB isn't allowing it to be dropped into a cell?

I didn't do much to the code other then define some variables to point the code where it needs to go, here it is....

Rich (BB code):
Sub Test()


Dim cCount As Long, gCount As Long
Dim vArr As Variant, v As Variant
Dim sF As String, sF1 As String, sF2 As String
Dim gItem As Range, cItem As Range
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet


Set ws1 = Sheets("Lookups")
Set ws2 = Sheets("Actuals Export")
Set ws3 = Sheets("Staging")


sF1 = "SumProduct (#g# * (IOHeader = RC4) * #c  # * (Hdata = R7C) * DataTable) / 1000"


Set cItem = ws1.Range("F23")
Set gItem = ws1.Range("F24")
cCount = 1
gCount = 3


vArr = GetPermutations(cCount, gCount)


    For Each v In vArr
            sF2 = sF1
            If v(0) = 0 Then sF2 = Replace(sF2, "#c  # *", "") Else sF2 = Replace(sF2, "#c  #", "(CData = " & cItem.Offset(0, v(0) - 1).Value & ")")
            If v(1) = 0 Then sF2 = Replace(sF2, "#g# *", "") Else sF2 = Replace(sF2, "#g#", "(GData = " & gItem.Offset(0, v(1) - 1).Value & ")")
        
            sF = sF & "+" & sF2
    
    Next v


sF = "=" & Mid(sF, 2)


Sheets(5).Range("I18").FormulaR1C1 = sF




End Sub
' generates the permutations given cCoutn and gCount
Function GetPermutations(cCount As Long, gCount As Long) As Variant


Dim i As Long, j As Long, k As Long
Dim vArr As Variant


ReDim vArr(1 To IIf(cCount = 0, 1, cCount) * IIf(gCount = 0, 1, gCount))


For j = IIf(cCount = 0, 0, 1) To cCount
    For k = IIf(gCount = 0, 0, 1) To gCount
        i = i + 1
        vArr(i) = VBA.Array(j, k)
    Next k
Next j


GetPermutations = vArr


End Function
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi

That error can be a syntax error in the formula.

What I'd do is to enter the correct expected formula in the cell, see that it works OK and read it in R1C1 notation.
For ex. in the immediate window use ?ActiveCell.FormulaR1C1

You can then compare it to the formula that the code generates.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,157
Messages
5,640,465
Members
417,144
Latest member
Pitsi Mokoele

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
Top