Solved: Prob: evaluate Method

[irresistible]

I have created a mock of my original sheet and attached here so as to ease for me and everyone.. Prob is that Evaluate Method (rather[...] method) isnt giving me the correct value instead of giving '8660' its giving '0'.. where have i gone wrong? :banghead:

Though the macro is there but still...here is the code in sheet 1:


Option Explicit
Private Sub CommandButton1_Click()
Dim myname As String
Dim mynum As Integer
Dim myval As Double
myname = "Mudassir"
mynum = 2000
myval = [SUMPRODUCT(--(A1:A7=" & myname & "), --(B1:B7>" & mynum & "), B1:B7)]
Cells(1, 5).Value = myval
End Sub

[johnske]

When using variables you shouldn't use the shortcut ([ ]) method, try Evaluate("SUMPRODUCT(--(A1:A7=""" & myname & """), --(B1:B7>" & mynum & "), B1:B7)")

[irresistible]

the code which you've mentioned is working for me but before i use it on my actual sheet, i need to understand the purpose of extra quotes appearing... can you please shed some light on the purpose of each quote mark? i appreciate your help!

[johnske]

if you write it without using a VBA variable, in shortcut notation you need...
[E1] = [SUMPRODUCT(--(A1:A7="Mudassir"), --(B1:B7>2000), B1:B7)]

if you write it without using a VBA variable and in 'long' notation you need a string...
[E1] = Evaluate("SUMPRODUCT(--(A1:A7=""Mudassir""), --(B1:B7>2000), B1:B7)")
the very 1st quotation mark in ""Mudassir"" closes the string for "SUMPRODUCT(--(A1:A7=, the 2nd one starts the string Mudassir, etc.

'if you write it using a VBA variable, it must be in string form and concatenated with the variable...
[E1] = Evaluate("SUMPRODUCT(--(A1:A7=""" & myname & """), --(B1:B7>" & mynum & "), B1:B7)")

'i.e. " & myname & " and (for the 2nd variable) " & mynum & " is needed for concatenation

HTH :)

[irresistible]

Great man... my actual workbook rather a full-fledged project is now running with flying colors... thanks dude