r/excel•
8BallRunOut

VBA Sumproduct not matching with variables

solved

I have the following code

x=Evaluate("=SUMPRODUCT(--(Candidates[First Name]=" & Me.cboFirstName & "),--(Candidates[Last Name]=" & Me.cboLastName & "),Candidates[CandidateNum])")

which always results in Error 2029 regardless of if there is a match in the data set or not. I should add that on hover, Me.cboFirstName = "John" and Me.cboLastName = "Smith"

Then I tried

x=Evaluate("=SUMPRODUCT(--(Candidates[First Name]=""John""),--(Candidates[Last Name]=""Smith""),Candidates[CandidateNum])")

Which does work, so what am I doing wrong?

Posted Mar 23, 2018, 1:43 PM UTC

24
itsnotaboutthecell111•

Try triple quotations -

(Candidates[First Name]=""" & Me.cboFirstName & """)

3
8BallRunOutOP71•

solution verified

wow, i hate VBA

2
Clippy_Office_AsstMOD•

You have awarded 1 point to itsnotaboutthecell

1
itsnotaboutthecell111•

I AGREE - haha... it's an interesting language for sure.

1