Andreas Hermle

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

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

Regards, Andreas

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'!$

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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.

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

glad to help.

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

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

=SUM(indirect(somewhere))

Put 'MyCustomSheetName'!$G$5:$