How do I assign "Apple" to a cell reference in this evaluate code?

[lhkittle]

Sub Cnt() correctly returns 16.
Sub Cnt1() either Msgbox line returns 0

Option Explicit

Sub Cnt()
MsgBox Evaluate("SUMIF(D27,""Apple"",E2:E7)")
End Sub

Sub Cnt1()
Dim i As Variant
i = Range("F1").Value
'MsgBox Evaluate("SUMIF(D27,""i"",E2:E7)")
MsgBox Evaluate("SUMIF(D27,i,E2:E7)")
End Sub

Thanks,
Regards,
Howard

 

[plinius]

Il 28/11/2012 09:31, (e-mail address removed) ha scritto:

Sub Cnt() correctly returns 16.
Sub Cnt1() either Msgbox line returns 0

Option Explicit

Sub Cnt()
MsgBox Evaluate("SUMIF(D27,""Apple"",E2:E7)")
End Sub

Sub Cnt1()
Dim i As Variant
i = Range("F1").Value
'MsgBox Evaluate("SUMIF(D27,""i"",E2:E7)")
MsgBox Evaluate("SUMIF(D27,i,E2:E7)")
End Sub

Thanks,
Regards,
Howard

You can use directly F1, or avoid "Evaluate"

Sub Cnt1()
MsgBox Evaluate("SUMIF(D27,F1,E2:E7)")
End Sub


Sub Cnt1()
Dim i As Variant
i = Range("F1").Value
MsgBox Application.SumIf([D27], i, [E2:E7])
End Sub


Hi,
E.

 

[lhkittle]

Il 28/11/2012 09:31, (e-mail address removed) ha scritto:

Sub Cnt() correctly returns 16.

Sub Cnt1() either Msgbox line returns 0

Option Explicit

Sub Cnt()

MsgBox Evaluate("SUMIF(D27,""Apple"",E2:E7)")

End Sub

Sub Cnt1()

Dim i As Variant

i = Range("F1").Value

'MsgBox Evaluate("SUMIF(D27,""i"",E2:E7)")

MsgBox Evaluate("SUMIF(D27,i,E2:E7)")

End Sub

You can use directly F1, or avoid "Evaluate"



Sub Cnt1()

MsgBox Evaluate("SUMIF(D27,F1,E2:E7)")

End Sub





Sub Cnt1()

Dim i As Variant

i = Range("F1").Value

MsgBox Application.SumIf([D27], i, [E2:E7])

End Sub





Hi,

E.

Spot On!!

Thanks plinius.