Hi,
Is there a simple way of defining a string variable that will be used in VBA
to insert a formula in a cell, the string variable containing sets of
quotation marks?
I am trying to get VBA to enter a formula in a cell at the end of a long
calculation. The formula is referencing a fixed cell with a name (easy),
followed by some text (also easy) followed by a TEXT function referencing a
(variable) cell containing a date value, and formatting this as "DD/MM/YY".
However, I cannot get the text function to work (as I am entering the cell
row as a variable). I have tried various combinations of using double
quotation marks or Chr(34) to enter the quotation marks but either VBA
objects to the string definition or I get a runtime error when inserting the
string as a formula in the destination cell (using FormString =
Range("destination cell").Formula, FormString being the string I have been
trying to define and "StartTime" being a named reference. The runtime error
occurs even with a fairly simple string of the format:
FormString = "=A7" & "&" & ", from " & "&" & "TEXT(StartTime," & Chr(34) &
"DD/MM/YY" & Chr(34) & ")" - or similar expressions (using &"""&... etc), let
alone when I try to exchange the named cell ("StartTime") with some
combination (say "Sheet1!a"&rownumber) in the formula. I know I could use
Index or Offset and get the macro simply to put the index or offset in a
cell, and then use a manually constructed formula in my target cell but I
would much prefer to write the formula in to the target cell as part of the
macro. I could also simply write the result as text to the target cell but
really want it there as a formula.
I had assumed this would be fairly trivial but it has defeated me so far...
Any help would be most appreciated.
Best wishes, Boris.