Translate to code

[Ray]

Hi -

I'd like to translate the following formula (which works in Excel) to
work in VBA code:
=SUMPRODUCT((LEFT($C$3:$C$100,6)="UNICEF")*($G$3:$G$100))
(Note: entered as an ARRAY formula)

Thanks, Ray

 

[Doug Glancy]

Ray,

Try this, assuming you want the formula entered in A3:

Range("A3").Formula = "=SUMPRODUCT((LEFT(C3:C100,6)=""UNICEF"")*(G3:G100))"

By the way, you don't have to enter that formula as an array formula.

hth,

Doug

 

[Ray]

Hi Doug -

I didn't clearly communicate what I actually needed to do ... sorry
for confusion. The requested formula will be part of a much larger
macro that is essentially transferring data from a daily 'dummy' file
to a standardized template. Most of the data being transferred is on
a one-to-one basis, so a simple application.vlookup works quite
nicely ... BUT several items starting with the word "UNICEF" will
exist in the base file, so there's a many-to-one relationship.

I was hoping to use application.sumif or application.sumproduct but
apparently these don't exist in VBA ... any ideas?

thanks for your time!

regards ,ray

 

[Doug Glancy]

Ray,

Most VBA- accessible Excel functions are accessed like:

Application.WorksheetFunction.SumIf or
Application.WorksheetFunction.SumProduct

hth,

Doug

 

[Gary''s Student]

Sub ray()
zum = 0
For i = 3 To 100
If Left(Cells(i, "C").Value, 6) = "UNICEF" Then
zum = zum + Cells(i, "G").Value
End If
Next
MsgBox (zum)
End Sub

 

[Ray]

GS -

A few more lines of code, but a little smoother I think ... plus, a
good solution applicable to many other situations I've seen!

Thanks to both of you for your time!

br/ray