Sumif in VB Code

[Mervyn Thomas]

Can anyone tell me what is wrong with this bit of code which is in a loop
with a counter variable?
I am trying to define a formula on worksheet(2) based upon ranges in a
worksheet called "Timesheet" Everywhere else I have always used FormulaR1C1
style but that does'nt fit in this case.


Worksheets(2).Cells(Counter, 8).Formula =
"=SUMIF(Timesheet!(Cells(5,8),Cells(5,256)),1,Timesheet!(Cells(Counter,8),Ce
lls(counter,256))"

 

[Patrick Molloy]

you can't use "cells" within a worksheet formula


Worksheets(2).Cells(Counter, 8).FormulaR1C1 =
"=SUMIF(Timesheet!R5C8:R5C256)),1,Timesheet!R" & _
counter & "C8:R" & counter & "C256))"

 

[Bob Phillips]

Mervyn,

A number of things. Firstly you need to differentiate and separate the
textual parts of formula and the values from your code, and you need to pass
formula an address string, not column and row numbers (that is FORMULAR1C!
but you need the RC ids as well). Here is a version that passes compilation
at least

sFormula = "=SUMIF(Timesheet!" & Range(Cells(5, 8), Cells(5,
256)).Address & " ,1,Timesheet!" & _
Range(Cells(Counter, 8), Cells(Counter, 256)).Address & ")"
Worksheets(2).Cells(Counter, 8).Formula = sFormula


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mervyn Thomas]

Thanks Patrick it works!
Thanks also Bob but I did'nt get round to trying that approach - I'm sure
its good!
Mervyn