Sumif in VB Code

  • Thread starter Thread starter Mervyn Thomas
  • Start date Start date
M

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))"
 
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))"
 
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)
 
Thanks Patrick it works!
Thanks also Bob but I did'nt get round to trying that approach - I'm sure
its good!
Mervyn
 
Back
Top