Applying Formula with Double Quotes not working

[shaunk]

I am constructing a formula programatically. The formula contains double quotes (for the TEXT function) which I am escapeing with the chr value for double quotes.

The build of the formula is this:
Dim Man_Ed_Formula As String
Man_Ed_Formula = "=DATEVALUE(TEXT(Employees!H2," & Chr(34) & "dd/mm/yyyy" & Chr(34) & "))"

So the formula should look like this:
=DATEVALUE(TEXT(Employees!H2,"dd/mm/yyyy"))

The code I use to apply the formula is this:
Range("B2").Offset(loopcounter, 0).Range("A1").FormulaR1C1 = Man_Ed_Formula

The string containing the formula(Man_Ed_Formula) looks fine before applying it, but it ends up putting single quotes around the H2, so the formula end up looking like this
=DATEVALUE(TEXT(Employees!'H2',"dd/mm/yyyy"))

This is invalid and I can't figure out why it is inserting the stray apostrophes.

Thanks

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[PHV]

Replace this:
Range("B2").Offset(loopcounter, 0).Range("A1").FormulaR1C1 = Man_Ed_Formula
with this:
Range("B2").Offset(loopcounter, 0).Range("A1").Formula = Man_Ed_Formula

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunk]

Worked like a charm...and so quick ....thankyou PHV

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[SkipVought]

So the formula should look like this:
=DATEVALUE(TEXT(Employees!H2,"dd/mm/yyyy"))

So let me understand.

1) Employees!H2 is a DATE, that you are

2) converting to a STRING, using the TEXT function and then

3) converting the STRING to a DATE, using the DATEVALUE function.

Does that not seem like a Rube Goldberg approch?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shaunk]

The simpler version of course is DATEVALUE(Employees!H2). But it is failing when the date in the field is '01/01/1900', with an invalid data type message.
So in keeping with Einstein's maxim of 'Make everything as simple as possible…..but no simpler', I employed the text function and it works.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[GlennUK]

Hi Shaunk,

Surely the simpler version would be:

=Employees!H2

without the DATEVALUE at all?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Exactly!!!

Employees!H2 is a DATE

Why DATEVALUE?????



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]