Excel VBA to insert a formula into a cell

Question

****UPDATE****

I managed to get the code to work, however, every time that i try to run it, in another workbook, it pops up with a dialogue box saying to update values. I don't want it to bring the values from the original sheet. I want it to use the cells that I have referenced and the sheets that I have referenced in the formula.

I am trying to figure out how to put a formula into a certain cell "I8" with a VBA code. I have tried several different ways of doing this. I researched online and found this code:

Worksheets("SheetName").Range("I8").Formula =

I can use this to do basic stuff like the sum of a cell range and stuff like that. What i need to put in there is extreemly long and it puts strings, new paragraphs, and cell references all in one.

The formula grabs a bunch of different information and puts it in a nice and neat email format so all I have to do is copy that cell "I8" and paste it in the body of an email.

Here is the formula that I have in cell "I8" that works. All I need to do is figure out how to make it work with the VBA code above:

=("Good " & $C$2) & CHAR(10) & CHAR(10) & References!C1 & CHAR(10) & CHAR(10) & "Service Channel WO#:  " & $C$4 & CHAR(10) & "Location:  " & $C$5 & CHAR(10) & "SLM Work Order Number:  " & $C$6 & CHAR(10) & CHAR(10) & References!C2 & $C$7 & CHAR(10) & CHAR(10) & References!C3 & CHAR(10) & CHAR(10) & References!C4

Any help is greatly appreciated as this is a very unique formula, and what I have found online isn't much help.

What actually happens if you try to put this into the cell using your code? You haven't told us what's actually wrong. — Martin
Looks like you should build it as a string instead of a formula — urdearboy

VBasic2008 VBasic2008 · Accepted Answer · 2018-08-22T22:36:11

The 'dialogue box saying to update values' issue is resolved if you create the References worksheet in the active workbook. If you can't do that you will have to discover a few more secrets of your project.

Important things in creating the string containing the formula: The string has to start with an equal sign (=). All quotes have to be replaced with chr(34). (In the way Excel uses the CHAR function, VBA uses the Chr function: CHAR(34) in Excel is similar to Chr(34) in VBA.) The result is the following code:

Sub TheFormula()
Dim Str1 As String
Dim rRange as Range
Set rRange = ActiveSheet.Range("I8")
Str1 = "=("
Str1 = Str1 & Chr(34) & "Good "
Str1 = Str1 & Chr(34) & " & $C$2) & CHAR(10) & CHAR(10) & References!C1 &"
Str1 = Str1 & " CHAR(10) & CHAR(10) & "
Str1 = Str1 & Chr(34) & "Service Channel WO#:  "
Str1 = Str1 & Chr(34) & " & $C$4 & CHAR(10) & "
Str1 = Str1 & Chr(34) & "Location:  "
Str1 = Str1 & Chr(34) & " & $C$5 & CHAR(10) & "
Str1 = Str1 & Chr(34) & "SLM Work Order Number:  "
Str1 = Str1 & Chr(34) & " & $C$6 & CHAR(10) & CHAR(10) & References!C2 &"
Str1 = Str1 & " $C$7 & CHAR(10) & CHAR(10) & References!C3 & CHAR(10) &"
Str1 = Str1 & " CHAR(10) & References!C4"
rRange.Formula = Str1
End Sub

I hope this was useful.

Excel VBA to insert a formula into a cell

1 Answers