CODEWITHSUNDEEP
vbaexcelformula
Béatrice
Béatrice

Reputation: 1

Add formula with VBA to range of cells

Dear Overflow community

I`d like to add a formula some cells (4, x), for each sheet (sh). I'm running the sub in a for-loop in the main code, which runs thru each column (a). I've tried many things but unfortunately my code just won't run: 

Sub InsertFormula(ByVal sh As Worksheet, ByVal a As Long)

Formel = "=TEXT(" & a & "$1;'TTT')"
sh.Cells(4, a).Formula = Formel

End Sub

EDIT:

Sub InsertFormula(ByVal sh As Worksheet, ByVal a As Long)

Formel = "=TEXT(R1C" & a & ",""TTT"")"
sh.Cells(4, a).FormulaR1C1 = Formel

End Sub

Thanks very much, working perfectly

Upvotes: 0

Views: 644

Answers (3)

Jayant Kumar jain
Jayant Kumar jain

Reputation: 27

Just try this to overcome your problem: put Formel directly without using keyword .formula

Sub InsertFormula(ByVal sh As Worksheet, ByVal a As Long)

Formel = "=TEXT(R1C" & a & ",""TTT"")"
sh.Cells(4, a) = Formel

End Sub

Upvotes: 0

Tom
Tom

Reputation: 9878

This takes the column letter as opposed to the column number. Also I've changed the formula to use " instead of ' and corrected the TEXT formula as these two are part of the reason the formula was failing 

Sub InsertFormula(ByVal sh As Worksheet, ByVal Col As String)
    Dim Formel As String

    Formel = "=TEXT(" & Col & "$1,""TTT"")"
    sh.Range(Col & "4").Formula = Formel

End Sub
Sub test()
    Call InsertFormula(ActiveSheet, "A")
End Sub

Upvotes: 1

arcadeprecinct
arcadeprecinct

Reputation: 3777

In VBA you need commas to separate function arguments in formulas regardless of your local settings. (You can get around it by using .FormulaLocal but I don't recommend it since it might not run on other machines)

Furthermore, as Shai Rado noted, you use a number as a column name. In this case I would use R1C1 format so you don't have to convert the number into a letter, i.e.

Formel = "=TEXT(R1C" & a & ",""TTT"")"
sh.Cells(4, a).FormulaR1C1 = Formel

Upvotes: 1

Related Questions