string substitution using Excel VBA (similarly to the Excel SUBSTITUTE function)

Question

I have a question regarding string modification. Let's assume that we have the following string: 4155595208***
in cell A1 (10 numbers and 3 starts).

Now I would like to substitute these 3 stars with a blank field and get the result in cell B1 (so I want 4155595208 to get as a result). If I used a normal built-in Excel function I would type the following thing into cell B1:
=SUBSTITUTE(A1,"*","")

Now I would like to do the very same thing using Excel VBA. Whenever I tried to run the following code however, I got the error message: "Run-time error '13': Type mismatch".

sub test()
cells(1,2) = "=SUBSTITUTE(M2," * ","")"
end sub

I guess the problem might occur because of the bunch of quotation marks but I don't know. Could someone help me with this matter?

Thanks in advance,
Laszlo

Michael · Accepted Answer · 2012-01-19 14:15:36Z

1

You need to use double double quotes when writing formulas

Cells(1, 2) = "=SUBSTITUTE(M2,"" * "","""")"

answered Jan 19, 2012 at 14:15

Michael

2,1651 gold badge21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

string substitution using Excel VBA (similarly to the Excel SUBSTITUTE function)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related