I'll get you started, you can do the rest yourself

"=IF(ISERROR(SUM(" & month1 & ":" & month3 & "!RC)/...

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"ewan7279" wrote in message
...
Hi All,

I have a macro that enters a formula into a sheet. The formula works with
actual sheet names, but I would like the sheet names to be variable in the
formula. I know I have to use quotation marks and ampersands to include
text
that will appear in the cell and variables gained from the macro, but I
can't
work out how.

the variables in the formula are month1, month2 and month3 and the formula
is in R1C1 format:

month1 = ActiveSheet.Name
some code
month2 = ActiveSheet.Name
some code
month3 = ActiveSheet.Name

Range("j19").Select
ActiveCell.FormulaR1C1 = _

"=if(iserror(SUM(month1:month3!RC)/(3-(COUNTIF(month1!RC,0)+COUNTIF(month2!RC,0)+COUNTIF (month3!RC,0)+COUNTIF(month1!RC,"""")+COUNTIF(mont h2!RC,"""")+COUNTIF(month3!RC,"""")))),0,SUM(month 1:month3!RC)/(3-(COUNTIF(month1!RC,0)+COUNTIF(month2!RC,0)+COUNTIF (month3!RC,0)+COUNTIF(month1!RC,"""")+COUNTIF(mont h2!RC,"""")+COUNTIF(month3!RC,""""))))"

Any ideas please...??