How to use formula or formulalocal in VBA

[Waimea]

I am trying to use formula or formulalocal in VBA but the following code snippets gives me a runtime error.

Worksheets("Test").Range("F10").Formula = "=IF(B4="All countires","*",B4)"

Worksheets("Test").Range("F10").Formula = "=IF(B4="All countires";"*";B4)"

Worksheets("Test").Range("F10").FormulaLocal = "=IF(B4="All countires";"*";B4)"

Worksheets("Test").Range("F10").FormulaLocal = "=IF(B4="All countires","*",B4)"

What am I doing wrong?

 

[Fluff]

You need to double up the quotes inside the formula

Worksheets("Test").Range("F10").Formula = "=IF(B4=""All countires"",""*"",B4)"

 

[Eric W]

When putting quotes within quotes, you need to double them to let the compiler know that they are included in the string, and not delimiters:

Worksheets("Test").Range("F10").Formula = "=IF(B4=[COLOR=#ff0000]""[/COLOR]All countires[COLOR=#ff0000]""[/COLOR],[COLOR=#ff0000]""[/COLOR]*[COLOR=#ff0000]""[/COLOR],B4)"

 

[RoryA]

Also, I imagine that should be countries and not countires?

 

[Waimea]

Thank you for your reply Fluff! It works when I double up the quotes!

 

[Waimea]

Yes, I got so excited I spelled it wrongly!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Waimea]

How would I loop through a range and place formulas in them using VBA?

Sub test()
Dim rng as range
set rng = Rnge("A2:D10")

Worksheets("Test").Range("E2:E10").Formula = "=SUM(A2:D10)"

End Sub

 

[Fluff]

What's wrong with what you have?

 

[Waimea]

I am really struggling with how to insert formulas in cells using VBA and loops and it would be very kind if someone can show me an example of how to insert two different formulas in two different columns.

Formula#1 ="SUM(A2:E10)"

Formula#2 ="AVERAGE(A2:E10)"

Say, formula#1 in column F in the range of F2:F10 and formula#2 in column G in the range of G2:G10.