Syntax / Compile error

[applebyd]

Hi all,

Havethe following which works fine as a formula in the workbook:

Range("AD2") = "=DATEVALUE(TRIM(MID(AE2,FIND(" ",AE2),3))&" "&LEFT(AE2,FIND(" ",AE2)-1)&" "&TRIM(MID(AE2,FIND(" ",AE2,FIND(" ",AE2)+1),5)))"

However, if I try and use it in VBA I am getting a syntax error (expected End of Statement) highlighted in the section ,AE2),3))&".

Not sure what is happening as if I put it in the workbook I can copy down quite happily. Just not when trying to do it programmatically.

My head hurts

Any help appreciated.

Thanks

DaveA

 

[louisH]

Hi,

Please let me release you from your headache.

It's because of the quote marks (")
You need to double the quote marks which are Inside of the formula to differentiate them from the start and end quote mark

Range("AD2").Formula = "=DATEVALUE(TRIM(MID(AE2,FIND("" "",AE2),3))&"" ""&LEFT(AE2,FIND("" "",AE2)-1)&"" ""&TRIM(MID(AE2,FIND("" "",AE2,FIND("" "",AE2)+1),5)))"

 

[applebyd]

Many thanks.

Works a treat.

I'd love to say, simple once you've pointed it out but......

It wasn't.

I'd never have got that in a month of Sundays.

Thanks again.

Regards

DaveA

 

[applebyd]

That said, it works fine provided the data has double digits for the date.

11th is fine but 1st is returning a #VALUE

Back to the drawing board methinks.

GURRRRRR..............

Regards

DaveA

 

[Norie]

DaveA

What are you trying to do with the formula?

 

[applebyd]

Norie.

I have a column (AE) that has a date in it in the format March 8, 2018 9:00:00 AM

This comes out of a data extract formatted as general. I cannot simply change format to DD/MMM/YYYY
as even though it says it's changed nothing is happening hence the attempt to do it the 'simple' way using DATEVALUE.

I'm beginning to think this may be the wrong approach, however, it's almost there!

Any ideas welcome!

 

[Norie]

Is it always the full month name?

 

[Norie]

This formula worked for me, though I'm sure it could be shortened.

=TRIM(MID(AE2,SEARCH(" ", AE2),SEARCH(",", AE2)-SEARCH(" ", AE2))&" "&LEFT(LEFT(AE2,SEARCH(",",AE2)-1),SEARCH(" ",AE2)-1)&MID(AE2,SEARCH(",",AE2)+1,LEN(AE2)))+0

To use it in code it would look like this.

Range("AD2").Formula ="=TRIM(MID(AE2,SEARCH("" "", AE2),SEARCH("","", AE2)-SEARCH("" "", AE2))&"" ""&LEFT(LEFT(AE2,SEARCH("","",AE2)-1),SEARCH("" "",AE2)-1)&MID(AE2,SEARCH("","",AE2)+1,LEN(AE2)))+0"

PS If you have Power Query available you could probably convert the column without a formula.

 

[applebyd]

Hi Norrie,

That worked a treat: I have a routine that now goes:

    Columns("AD:AD").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "Date Created"
    Range("AD2").Select
    
    
  Range("AD2").Formula = "=TRIM(MID(AE2,SEARCH("" "", AE2),SEARCH("","", AE2)-SEARCH("" "", AE2))&"" ""&LEFT(LEFT(AE2,SEARCH("","",AE2)-1),SEARCH("" "",AE2)-1)&MID(AE2,SEARCH("","",AE2)+1,LEN(AE2)))+0"
    
    
    Selection.NumberFormat = "m/d/yyyy"
    Selection.AutoFill Destination:=Range("AD2:AD" & LastRow)

This works perfectly for the four columns I need.

Now....

All I am doing next is copying ColAD and pasting it back as values.

If I do this on the worksheet manually it works fine. However, if I do it in VBA it seems to be taking the value
from Cell AD2 and pasting it down.

I've even tried recording the code whilst doing it. This again works perfectly on the worksheet but
not in VBA.

 Columns("AD:AD").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I also tried:

Columns("AD:AD").Value = Columns("AD:AD").Value

This produces EXACTLY the same. The whole column populates with the value from AD2.

I've been wrestling with this all day, and, can't see an obvious error anywhere. The
code executes completely, just doesn't paste back the correct value.

Really not sure what's going on as it seems a bit bizarre.

Any ideas anyone?

Thanks

DaveA

 

[Norie]

Try this.

    Columns("AD:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AD1").Value = "Date Created"
    
    
    With Range("AD2:AD" & LastRow)
        .Formula = "=TRIM(MID(AE2,SEARCH("" "", AE2),SEARCH("","", AE2)-SEARCH("" "", AE2))&"" ""&LEFT(LEFT(AE2,SEARCH("","",AE2)-1),SEARCH("" "",AE2)-1)&MID(AE2,SEARCH("","",AE2)+1,LEN(AE2)))+0"
        .Value = .Value
        .NumberFormat = "m/d/yyyy"
    End With