Creating a formula in VBA with quoted strings

  • Thread starter Thread starter Thomas Philips
  • Start date Start date
T

Thomas Philips

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

=SUBTOTAL(1,F126:F127)

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
=text(SUBTOTAL(1,F126:F127),"0.00%")

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.

Sincerely

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.
 
Back
Top