Creating a formula in VBA with quoted strings


Thomas Philips

I have a cell with the following formula in cell F128 (i.e. cells(128,6):


I'd like to convert this formula to the following
=text(SUBTOTAL(1,F126:F127),"0.00%") & "/" & text(x,"0.00%")

where x is a real number, and so that the contents of cell F128 become something like
2.00% / 2.50%

I can get some, but not all of this to work in VBA. In particular, if I write
cells(128,6).formula = "=text(" & Mid(Trim(Cells(128, 6).Formula), 2) & ", ""0.00%"" )"

the formula in cell F128 becomes

which solves the first part of my problem, but i I extend it to

cells(128,6).formula = "=text(" & Mid(Trim(Cells(128, 6).Formula), 2) & ", ""0.00%"" )" & "" / ""

it chokes and gives me an run-time error 1004 - Application-defined or object-defined error. I'd be very appreciative if someone could point out the flaw in my method, and point me in the right direction.


Thomas Philips




Figured it out by trying out stuff from other posts in this group:

Cells(BottomRow, 6).Formula = "=text(" & Mid(Trim(Cells(BottomRow, 6).Formula), 2) & ", ""0.00%"" )" & " & " & Chr(34) & " / " & Chr(34) & " & text(" & x & ", ""0.00%"" )"

where x is a real number.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question