Solved

# Enter a formula in worksheet using VBA

Posted on 2013-12-27
338 Views
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
0
Question by:AndreasHermle

LVL 13

Assisted Solution

Alexander Eßer [Alex140181] earned 50 total points
ID: 39741658
If you want to add formulas to your sheets using VBA, "just" put them into the "Formula" property of the corresponding Cell/Range object(s), as shown here:
http://msdn.microsoft.com/en-us/library/office/aa221602%28v=office.11%29.aspx
0

LVL 48

Assisted Solution

Rgonzo1971 earned 50 total points
ID: 39741667
Hi,

pls try

``````Sub Macro2()
srcSht = Sheets("MainSheet").Range("A17").Value
If srcSht = "" Then
MsgBox "No Sheet choosed:  Exiting..."
Exit Sub
End If
Set myRange = Sheets(srcSht).Range(Range("G5"), Range("G" & Rows.Count).End(xlUp))
Sheets("MainSheet").Range("B20").Formula = "=SUM('" & srcSht & "'!" & myRange.Address & ")"
End Sub
``````
Regards
0

LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 400 total points
ID: 39741696
You will need to build the string to assign to the formula programmatically.

You would first need to check to see whether the value of 'MainSheet' cell A17 is blank

strSheet = ActiveWorkbook.sheets("MainSheet").Range("A17").Value
if strSheet = "" Then Exit Sub

To get the last row used in a column, you can use code similar to:

LastRow = Activesheet.Range("G65000").end(xlup).row

Then you would build the formula string

strFormula = "=SUM('" & strSheet & "'!\$G\$5:\$G\$" & LastRow & ")"
ActiveWorkbook.sheets("MainSheet").Range("B20").Formula = strFormula

String all of that together in a macro, subroutine, or function to get what you need.
0

LVL 4

Expert Comment

ID: 39741712
Just can use the formula indirect is okay

=SUM(indirect(somewhere))

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

Author Comment

ID: 39741804
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.
0

LVL 47

Expert Comment

ID: 39741819
0

Author Closing Comment

ID: 39744381
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
0

## Featured Post

### Suggested Solutions

Excel 2013 Problem 12 48
Gantt chart 2 16
Excel 2016 Hiding Toolbars 7 22
3rd level dependant list 4 33
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.