Link to home
Create AccountLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

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
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Just can use the formula indirect is okay

=SUM(indirect(somewhere))

Put    'MyCustomSheetName'!$G$5:$G$259  on somewhere ....
Avatar of 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.
glad to help.
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