Coverting Formula to VBA

[brettvba]

Hi People Just wondering if anyone can help im trying to convert this formula to VBA


=SUMIF(Christchurch!C:C,D5,Christchurch!D:D)+SUMPRODUCT((Christchurch!C2:C6000=D5)+0,(Christchurch!F2:F6000/Christchurch!M2:M6000))

So far I have

Sub Test()
ShtNm = "Christchurch"
SuppName = "John"
EndRow = 6000
FormValue = Application.WorksheetFunction.SumIf(Sheets(ShtNm).Columns("C:C") _
, SuppName, Sheets(ShtNm).Columns("d:d")) + Application.WorksheetFunction.SumProduct((Sheets(ShtNm).Range("C2:C") & EndRow = SuppName + 0), Sheets(ShtNm).Range("f2:f") & EndRow / Sheets(ShtNm).Range("m2:m") & EndRow)
End Sub


It just doesn't like me today any Ideas?

Brett

 

[Juan Pablo González]

Change this (And its similars)

Range("m2:m") & EndRow

to this

Range("m2:m" & EndRow)

 

[brettvba]

ok thanks Juan, I now have a type mismatch with what Ive got any ideas?



Sub Test()
ShtNm = "Christchurch"
SuppName = "John"
EndRow = 6000
FormValue1 = Application.WorksheetFunction.SumIf(Sheets(ShtNm).Columns("C:C") _
, SuppName, Sheets(ShtNm).Columns("d:d")) + Application.WorksheetFunction.SumProduct((Sheets(ShtNm).Range("C2:C" & EndRow) = SuppName) + 0, Sheets(ShtNm).Range("f2:f" & EndRow) / Sheets(ShtNm).Range("m2:m" & EndRow))

End Sub



Brett

 

[brettvba]

Anyone?