should be simple, but formula does not work in VBA

[pphilos]

Hello


This works as a formula in a cell on the worksheet Sheet1

I want to check 3 criteria

Date in column c on sheet2 is within last 12 months
name matches sheet1 b.58 or Combobox6.value
the letter "S" in column D

=SUMPRODUCT(--(Sheet2!$C$5:$C$3000>$B$1-365),--(Sheet2!$B$5:$B$3000=Sheet1!B58),--(Sheet2!$D$5:$D$3000="S"))

However, I would like to perform the same calculation in VBA and display the result on userform 1, TextBox7.

can anyone convert this, so I can use it in a module?

I have tried enclosing in "" but this does not work.

 

[jindon]

Hi
have you tried

userform1.textbox7 = Evaluate("SUMPRODUCT(--(Sheet2!$C$5:$C$3000>$B$1-365),--(Sheet2!$B$5:$B$3000=Sheet1!B58),--(Sheet2!$D$5:$D$3000=""S""))")

 

[pphilos]

good news

I did try the evaluate method, but I must have made a mistake somewhere. Thanks for this. However, I would like to use combobox6.value on userform1 instead of Sheet1!B58 in the sumproduct formula.

can I add it straight in or will I have to Dim Array and all that.

Kind regards

Phil