VBA macro with formula showing (Excel)

Question

I have a code in my macro that return me in my Cell(A5) result like #name?

Range("A5").Select
ActiveCell.FormulaR1C1 = "=Application.Sum(Range(Cells(1, 5), Cells(20, 5)))"
End Sub

#name? include a formula of text between quotation marks in my VB code

=Application.Sum(Range(Cells(1, 5), Cells(20, 5)))

If i will write without quotation marks it will returns me a result.(sum of range)

ActiveCell.FormulaR1C1 = Application.Sum(Range(Cells(1, 5), Cells(20, 5))) 

but I need to have in my cell A5 result and formula, how i got this result. Maybe it's enough to change some options?

Answer 1

Since you want to use a VBA code to enter a formula in Cell A5, then you need to use the right parameters:

Range(Cells(1, 5), Cells(20, 5)) is VBA, if you want it as a formula that an Excel sheet's cell recognizes, you need to use R1C5:R20C5.

Also, instead of Application.Sum just use Sum.

You should try avoid using Select and ActiveCell and directly use Range("A5").FormulaR1C1

So just use the line code below:

Range("A5").FormulaR1C1 = "=Sum(R1C5:R20C5)"

Answer 2

I found it simpler to add a formula to the cell using

 Sub addFormula()

  Dim ws As Worksheet 
  Set ws = Worksheets("Sheet1")

    With ws
        .Range("A5").NumberFormat = "General" 'This formats A5 as general
        .Range("A5").Formula = "=SUM(F1:F20)"
    End With

 End Sub

All it does is enters the formula into the cell A5

Answer 3

if you want to have a formula in your cell pointing to some range you define in your VBA code, then you can use

Range("A5").FormulaR1C1 = "=Sum(" & Range(Cells(1, 5), Cells(20, 5)).Address(, , xlR1C1) & ")"

Answer 4

If I understood correctly, you want to show in A5 both the formula and the result. If so, use

Dim aux As Variant
aux = Application.Sum(Range(Cells(1, 5), Cells(20, 5)))
ActiveCell.Value = "Application.Sum(Range(Cells(1, 5), Cells(20, 5))) : " & CStr(aux)

This code caould likely be improved (using Sum instead of Application.Sum, e.g.)