Formula in macro with variable sheet names

[Guest]

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(month2!RC,"""")+COUNTIF(month3!RC,"""")))),0,SUM(month1:month3!RC)/(3-(COUNTIF(month1!RC,0)+COUNTIF(month2!RC,0)+COUNTIF(month3!RC,0)+COUNTIF(month1!RC,"""")+COUNTIF(month2!RC,"""")+COUNTIF(month3!RC,""""))))"

Any ideas please...??

 

[Bob Phillips]

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)

 

[Guest]

Hi Bob,

Thanks for the reply. Below is what I have, but it still does not work. I
have copied the formula into my spreadsheet and replaced all the variable
sheet names with the actual sheet names, removed the extra quotation marks
and the ampersands, and the formula works. This suggests the formula is
correct, but something still isn't working when the macro puts it into the
sheet:

FORMULA IN CODE:

"=if(iserror(SUM(" & month1 & ":" & month3 & "!j19)/(3-(COUNTIF(" & month1 &
"!" & "j19,0)+COUNTIF(" & month2 & "!" & "j19,0)+COUNTIF(" & month3 &
"!j19,0)+COUNTIF(" & month1 & "!j19,"""")+COUNTIF(" & month2 &
"!j19,"""")+COUNTIF(" & month3 & "!j19,"""")))),0,SUM(" & month1 & ":" &
month3 & "!j19)/(3-(COUNTIF(" & month1 & "!j19,0)+COUNTIF(" & month2 &
"!j19,0)+COUNTIF(" & month3 & "!j19,0)+COUNTIF(" & month1 &
"!j19,"""")+COUNTIF(" & month2 & "!j19,"""")+COUNTIF(" & month3 &
"!j19,""""))))"

FORMULA IN SPREADSHEET:

=IF(ISERROR(SUM( essen : london!J19)/(3-(COUNTIF( essen!J19,0)+COUNTIF(
swindon!J19,0)+COUNTIF( london!J19,0)+COUNTIF( essen!J19,"")+COUNTIF(
swindon!J19,"")+COUNTIF( london!J19,"")))),0,SUM( essen :
london!J19)/(3-(COUNTIF( essen!J19,0)+COUNTIF( swindon!J19,0)+COUNTIF(
london!J19,0)+COUNTIF( essen!J19,"")+COUNTIF( swindon!J19,"")+COUNTIF(
london!J19,""))))

 

[Guest]

I worked it out:- I had to include apostrophes to wrap the sheet names
because some of the variables I had been using consisted of a number of words
i.e. 'Cost Centre Summary'. Without the apostrophes to wrap this as one
reference, Excel didn't like it. Finished result was:

"=if(iserror(SUM('" & month1 & ":" & month3 & "'!j19)/(3-(COUNTIF('" &
month1 & "'!" & "j19,0)+COUNTIF('" & month2 & "'!" & "j19,0)+COUNTIF('" &
month3 & "'!j19,0)+COUNTIF('" & month1 & "'!j19,"""")+COUNTIF('" & month2 &
"'!j19,"""")+COUNTIF('" & month3 & "'!j19,"""")))),0,SUM('" & month1 & ":" &
month3 & "'!j19)/(3-(COUNTIF('" & month1 & "'!j19,0)+COUNTIF('" & month2 &
"'!j19,0)+COUNTIF('" & month3 & "'!j19,0)+COUNTIF('" & month1 &
"'!j19,"""")+COUNTIF('" & month2 & "'!j19,"""")+COUNTIF('" & month3 &
"'!j19,""""))))"

 

[Bob Phillips]

Sorry, one of the most common problems, I should have anticipated it.

--
---
HTH

Bob

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