SumProduct/Concatenate/VBA Question

zdodson

Board Regular
Joined
Feb 29, 2012
Messages
124
Hello,


I am working within a VBA userform that has two comboboxes:

cboYourName
cboOppName

Once values have been selected from these comboboxes, it will return a value in the subsequent textbox that is based on this formula:

=CONCATENATE("AFF: ",SUMPRODUCT(--($W$2:$W$13=$W$18),--($X:$X=$X$18)),"/NEG: ",SUMPRODUCT(--($W:$W=$X$18),--($X:$X=$W$18)))

The formula works if it is in the worksheet itself, but I am having some trouble once I put it in to VBA. The "$W$18" and "$X$18" criterion I need to be the values of cboYourName and cboOppName respectively.

Can someone point me in the right direction on how to programmatically input sumproduct formulas in Excel VBA?


Thanks,

Zack
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Zack

What have you tried for the formula in VBA?

You should probably drop the CONCATENATE part of it because you can do that directly in the code.

Also, I think you need to check the SUMPRODUCTS.

The first one doesn't look right as the ranges aren't of the same size, which they need to be for SUMPRODUCT.
 
Upvote 0
Norie,


Whoops, you're right. There was an obvious syntax error in what I posted.

Here is what I was working with in the end:

Formula version:

=CONCATENATE("AFF: ",SUMPRODUCT(--($W:$W=A1),--($X:$X=B1)),"/NEG: ",SUMPRODUCT(--($W:$W=B1),--($X:$X=A1)))

VBA version:
Code:
Private Sub cboYourName_Change()


If Me.cboYourName.Value <> "" And Me.cboOppName <> "" Then
    Me.txtAFCount.Value = "AFF: " & Evaluate("=SumProduct(($W:$W=" & Me.cboYourName & ")*($X:$X=" & Me.cboOppName & "))") & _
  "/NEG: " & Evaluate("=SUMPRODUCT(($W:$W = " & Me.cboOppName & ")*($X$X=" & Me.cboYourName & "))")
    End If
    
End Sub
 
Last edited:
Upvote 0
That looks like what I was going to suggest, thought I would probably have split things up a bit so they can be error checked.

Anyway, is it working?
 
Upvote 0
It's not, unfortunately, the formula is correct (I believe), but for one reason or another, it is not dropping a value in the txtAFCount textbox (i.e. it is still blank after I select records from both combo boxes). :(
 
Upvote 0
Have you checked the code is actually being executed?

You could do that by putting a breakpoint (F9) on the If, then open the userform and make a selection on both comboboxes.

Then when the code breaks you can step through it with F8 to see what's happening.
 
Upvote 0
Took you up on your suggestion. The IF statement works, but the sumproduct portion returns a Run-time error 13: Type mismatch error statement. Any ideas?
 
Upvote 0
Sounds like the Evaluates could be failing.

Why not try splitting things up a bit?

Then when you step through the code you can check things.
Code:
If Me.cboYourName.Value <> "" And Me.cboOppName <> "" Then

    valAFF = Evaluate("=SumProduct(($W:$W=" & Me.cboYourName & ")*($X:$X=" & Me.cboOppName & "))")
    valNEG = Evaluate("=SUMPRODUCT(($W:$W = " & Me.cboOppName & ")*($X$X=" & Me.cboYourName & "))")

    Me.txtAFCount.Value = "AFF: " & valAFF &  "/NEG: " & valNEG

End If
 
Upvote 0
Unfortunately, the code that you gave me returns the same error message.

Good news, after working on it for an hour or two, I came up with a solution using the COUNTIFS function and your variables. Keep in mind, I was working with two comboboxes.

For the first combo box
Code:
Private Sub cboYourName_Change()
Dim WF As WorksheetFunction
Dim WS As Worksheet
Set WF = Application.WorksheetFunction
Set WS = Sheets("J_ComData")
    
'Checks to see if the same debater is selected for both
If Me.cboYourName.Value <> "" Then
    Me.txtAFCount.Value = "No records found"
    End If
    
End Sub

For the second combo box:
Code:
Private Sub cboOppName_Change()
Dim WF As WorksheetFunction
Dim WS As Worksheet
Set WF = Application.WorksheetFunction
Set WS = Sheets("J_ComData")


'Conditional function to see if both values are present, returns result
If Me.cboYourName.Value <> "" And Me.cboOppName <> "" Then


    valAFF = WF.CountIfs(WS.Range("$W:$W"), Me.cboYourName.Value, WS.Range("$X:$X"), Me.cboOppName.Value)
    valNEG = WF.CountIfs(WS.Range("$W:$W"), Me.cboOppName.Value, WS.Range("$X:$X"), Me.cboYourName.Value)
    
    Me.txtAFCount.Value = "AFF: " & valAFF & "/NEG: " & valNEG
    ElseIf Me.cboYourName.Value = "" Or Me.cboOppName.Value = "" Then
        Me.txtAFCount.Value = "No records found"
    End If

'Checks to see if there are duplicate entries for the combo boxes
If Me.cboOppName.Value = Me.cboYourName.Value Then
    MsgBox "Please select a different opponent", vbOKOnly
    Me.cboOppName.Value = ""
    Me.cboOppName.SetFocus
    End If
    
    
If valAFF = 0 Or valNEG = 0 Then
    Me.txtAFCount.Value = "No records found"
    End If
End Sub

Thanks your help, Norie! :)
 
Upvote 0
The code I posted wasn't meant to fix the error, it was meant to make it easier to find out why it was happening.

One thing I notice that in your new code you are using the Value property of the textboxes in the COUNTIFS.

Wonder if adding .Value would have fixed the SUMPRODUCTS.:)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top