|
Formula in macro with variable sheet names
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...?? |
Formula in macro with variable sheet names
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...?? |
Formula in macro with variable sheet names
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,"")))) "Bob Phillips" wrote: 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...?? |
Formula in macro with variable sheet names
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,""""))))" "ewan7279" wrote: 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,"")))) "Bob Phillips" wrote: 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...?? |
Formula in macro with variable sheet names
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) "ewan7279" wrote in message ... 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,""""))))" "ewan7279" wrote: 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,"")))) "Bob Phillips" wrote: 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...?? |
| All times are GMT +1. The time now is 11:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com