asked on

Enter a formula in worksheet using VBA

Dear Experts:

My current workbook has several worksheets.

On the first worksheet named 'MainSheet' (current worksheet) ...
... I need to enter the following formula into 'B20' using a VBA macro.

=SUM('MyCustomSheetName'!$G$5:$G$259)

The requirements:
1) The Name of 'MyCustomSheetName' worksheet is variable, i.e. this name is to be taken from the cell value of 'A17' of the 'MainSheet' worksheet. If the cell value of 'A17' is blank, the macro has to say so and will exit.

2) The range of the sum always starts at G5 but the End Range Value varies, i.e. it could be $G$5:$G$3574 or some other End Range value.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrew_man

Just can use the formula indirect is okay

=SUM(indirect(somewhere))

Put 'MyCustomSheetName'!$G$5:$G$259 on somewhere ....

Andreas Hermle

ASKER

Hi Rgonzo: Thank you very much for your swift and professional support. I am afraid to tell you that your code throws an 1004 error message and the error trapping says that the range is not created on the 'srcSht' Worksheet but on the main Sheet. Any idea why?

Hi Dale: your code works great. Thank you very much for it.

Dale Fye

glad to help.

Andreas Hermle

ASKER

Dear All,

thank you very much for your great support. I can use parts of all the answers and integrate it into my final code. Thank you very much for your professional help.

I really appreciate it.
Regards, Andreas