VBA Sumproduct not matching with variables
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
Try triple quotations -
(Candidates[First Name]=""" & Me.cboFirstName & """)
solution verified
wow, i hate VBA
You have awarded 1 point to itsnotaboutthecell
I AGREE - haha... it's an interesting language for sure.