formula strings containing quotation marks - VBA

[Boris]

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.

 

[Tom Ogilvy]

if you wantet to put in th Formula

=A7&", From "&Text(Starttime,"DD/MM/YY")

you would use

formstring = "=A7&"", From ""&Text(Starttime,""DD/MM/YY"")"

This assumes startTime is a defined name. Within the string, you double up
the double quotes.

 

[Boris]

Dear Tom,
Many thanks for your reply. I though I had tried that but perhaps I have
lost my way with keeping track of all the double and single quotation
marks... I will try again, starting with the simple code (as in the example
you corrected for me) and working up from there... I am reassured to know at
least that this is how it should work.
Best wishes, Boris.

 

[Boris]

Sorry Tom, I realise I am being a bit slow but... although your formula works
very well, I now cannot get the second half of my intended formula to work.
Basically, I want to reference a different cell which I now to be in column a
in worksheet 1 and the row number is available as one on my variables within
the excel code. I thought I would be able to use the same construction, but
replace the Text(starttime,...) with
Text(sheet1!a&rownumber&"",""&""DD/MM/YY""&"")""" (or similar variations on
this) but that still doesn't work (though the string it produces looks quite
promising). Can I possibly impose on you to give me yet some more help? Or
perhaps I need to change the construction to one using index or define my
target cell address as another variable?
Many thanks again and best wishes, Boris.

 

[Tom Ogilvy]

formstring = "=A7&"", From ""&Text(Sheet1!A" _
& rownumber & ",""DD/MM/YY"")"

a good way to test these it to go to the VBE and make the immediate window
visible for the view menu. then in the immediate window set your variable
value;

rownumber = 10

' now put in a question mark and you string formula to see how it will be
resolved:

? "=A7&"", From ""&Text(Sheet1!A" _
& rownumber & ",""DD/MM/YY"")"
=A7&", From "&Text(Sheet1!A10,"DD/MM/YY")

or you could type in

Activecell.Formula = "=A7&"", From ""&Text(Sheet1!A" _
& rownumber & ",""DD/MM/YY"")"

then go look at the activecell if you don't get an error.

 

[Boris]

Thanks Tom, that does it... not entirely sure I yet fully understand when to
use single and when to use double quotes but I will get thre!
Many many thanks for your help.
Best wishes, Boris.