Inserting "Substitute function" using VBA

[mathsbeauty]

Hello! I want to insert substitute function in excel worksheet. I have tried the following

Range("B1").Formula = "=Substitute(A1,"p","q")"

or

Range("B1").Formula = Application.WorksheetFunction.Substitute(A1, "p", "q")

In first case I get error . In second case i don't get any error, but the formula doesn't get inserted in the cell. Any help?

 

[pbornemeier]

You were close!
Turn on macro recorder. Click in B1. Press Enter.

This is recorded:

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""p"",""q"")"
    Range("B2").Select

Which can be simplified to this:

Range("B1").FormulaR1C1 = "=SUBSTITUTE(RC[-1],""p"",""q"")"

 

[Peter_SSs]

And if you know your formula is going in to B1 and is referring to A1 you can also do it without the R1C1 notation - but the double-quotes were your real issue.

Range("B1").Formula = "=Substitute(A1,""p"",""q"")"

 

[mathsbeauty]

Thanks Pbornemeier! It works. I forgot basics. Taking the help of macro recorder. I modified it as
Range("B1").Formula = "=Substitute(A1,""p"",""q"")"

 

[mathsbeauty]

There is another trouble.

Sub macro19()
Dim val As Variant
val = Application.WorksheetFunction.Substitute(a1, "p", "q")
MsgBox val
End Sub

Why nothing happens to val?

 

[Rick Rothstein]

This is not a worksheet formula, so you cannot refer to a range by its address (VB thinks it is a variable name). This is how you would write the code you posted...

Sub macro19()
  Dim val As Variant
  val = Application.WorksheetFunction.Substitute(Range("a1"), "p", "q")
  MsgBox val
End Sub

However, while VB is tolerating it, you should not use val as a variable name because it is the name of a built-in VB function... the Val function converts string values of numbers to actual numbers (look it up in the VB Help Files).

 

[mathsbeauty]

Thanks Rick Rothstein! It cleared my concept.