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.

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.

--
Rgards,
Tom Ogilvy


"Boris" wrote:

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.

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.

"Tom Ogilvy" wrote:

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.

--
Rgards,
Tom Ogilvy


"Boris" wrote:

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.

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" wrote:

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.

--
Rgards,
Tom Ogilvy


"Boris" wrote:

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.

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.

--
Regards,
Tom Ogilvy



"Boris" wrote:

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" wrote:

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.

--
Rgards,
Tom Ogilvy


"Boris" wrote:

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.

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.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy



"Boris" wrote:

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" wrote:

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.

--
Rgards,
Tom Ogilvy


"Boris" wrote:

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.