Inserting formula with a macro

charliew

Board Regular
Joined
Feb 20, 2018
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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...

VBA Code:
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,599
Office Version
  1. 2016
Platform
  1. Windows
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

 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry Zot commented on this before me.
 

charliew

Board Regular
Joined
Feb 20, 2018
Messages
63
Office Version
  1. 365
Platform
  1. Windows
Thank you both for your help, however i'm still getting an error...

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,599
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,487
Messages
5,706,663
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top