Sumif in VB Code
 

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,Time sheet!(Cells(Counter,8),Ce
lls(counter,256))"

Sumif in VB Code
 

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))"


-----Original Message-----
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,Tim esheet!
(Cells(Counter,8),Ce
lls(counter,256))"



.

Sumif in VB Code
 

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" wrote in message
...
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,Time sheet!(Cells(Counter,8),Ce
lls(counter,256))"

Sumif in VB Code
 

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

"Patrick Molloy" wrote in message
...
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))"


-----Original Message-----
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,Tim esheet!
(Cells(Counter,8),Ce
lls(counter,256))"



.