Inserting formula with a macro

[charliew]

Hi there,

I have tried searching other threads but they were all too old and didn't really answer my problem...

I have a command button that when i click it needs to insert a formula into a target cell, i have done this with another button in the same sheet and it works fine but VBA has a problem with the commas in the formula and I don't know how to get around it...

Private Sub CommandButton1_Click()

Range("A2").Value = Range("R1").Value
Range("D7").Formula = "=LEFT(D15,6)&(RIGHT(B3,6))"
Range.("D15").Formula = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND(",",A10&",")-1)," ",""),6))"


End Sub

The D7 formula works perfectly but the D15 formula is having a paddy. Any help would be greatly appreciated. I'm sure its a problem with my formula rather than VBA but i've tried a few things and i just can't get it to do what i need it to do.

Thank you.

 

[kanadaaa]

Try this:

Dim DBQ as String
DBQ = """"
Range("D15") = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND("& DBQ & "," & DBQ & ",A10&" & _
    DBQ & "," & DBQ & ")-1)," & DBQ & " " & DBQ & "," & DBQ & DBQ & "),6))" & DBQ

 

[Zot]

You have an illegal dot Range.("D15").Formula = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND(",",A10&",")-1)," ",""),6))"

Try this

Range("D15").Formula = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND("","",A10&"","")-1),"" "",""""),6))"

For every " in formula you should add another one to become ""

Reference

Put Double Quotes in a String in Excel VBA - ExcelBaby

You will learn: How to put double quotes in a string that already has a quote or double quote in Excel VBA. Take this free course now!

www.excelhowto.com

 

[kanadaaa]

Range.("D15").Formula = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND(",",A10&",")-1)," ",""),6))"

BTW I don't know why I didn't mention this first but the quoted part doesn't work just because there is a redundant dot after Range.

 

[kanadaaa]

Sorry Zot commented on this before me.

 

[charliew]

Thank you both for your help, however i'm still getting an error...

Range("D15").Formula = "=UPPER(LEFT(SUBSTITUTE(LEFT(A10,FIND("","",A10&"","")-1),"" "",""""),6))"

this is what i have, and i'm getting a 1004 error with that line highlighted.

is it just that type of formula wont work in VBA?

 

[Zot]

Computer can give unrelated error but in this case if the error is correct it has nothing to do with the formula. In fact I've created a button and assigned your doce to it. It executed your code and it wrote the formula in designated location.

Error 1004 is due to formula is referring to range or cell that does not exist but I have no idea why at this moment. Are you using UserForm or just ActiveX button. I was using ActiveX. If you use Userform, then the code did not have reference to worksheet. This may cause error.

 

[Fluff]

i'm getting a 1004 error with that line highlighted.

What was the error message?