1

I have a two different dates format in a column when I try to use that formula in vba it gives Application - defined object error ..

Ex : Date Format

9/5/2018 17:51 ==> This May 9th 2018

17/07/2018 15:45:20 ==> This is July 17th 2018

Formula : 

=IF(ISNUMBER(DAY(N2)),DAY(N2),MID(N2,FIND("/",N2,1)+1,FIND("/20",N2,1)-4))&"/"&IF(ISNUMBER(MONTH(N2)),MONTH(N2),MID(N2,1,FIND("/",N2,1)-1))&"/"&IF(ISNUMBER(N2),YEAR(N2),MID(N2,FIND("/20",N2,1)+1,4))


Sub test()
Dim LastRow As Long
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow

           ActiveSheet.Range(i, 2).Formula = " If(ISNUMBER (DAY(Range(""D"" & i))),DAY(Range(""D"" & i)),MID(Range(""D"" & i),FIND(""/"",Range(""D"" & i),1)+1,FIND(""/20"",Range(""D"" & i),1)-4))&""/""&IF(ISNUMBER(MONTH(Range(""D"" & i))),MONTH(Range(""D"" & i)),MID(Range(""D"" & i),1,FIND(""/"",Range(""D"" & i),1)-1))&""/""&IF(ISNUMBER(Range(""D"" & i)),YEAR(Range(""D"" & i)),MID(Range(""D"" & i),FIND(""/20"",Range(""D"" & i),1)+1,4))"
Next i
End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Maha
  • 25
  • 2
  • 6
  • 1
    Since you are using dates, why are you not using `IsDate`? – GMalc Aug 21 '18 at 02:02
  • Can you help me to correct above vba .. I tried isnumber @GMalc – Maha Aug 21 '18 at 02:33
  • It is late, i will get back on SO tomorrow, but please read through these SO question/answers they may help you understand better. [IsNumber vs IsDate](https://stackoverflow.com/questions/4338025/isdate-function-returns-unexpected-results) and [IsDate](https://stackoverflow.com/questions/4338025/isdate-function-returns-unexpected-results) – GMalc Aug 21 '18 at 03:04
  • what are you trying to do? if you are using VBA, you don't need to play with formulas – Ibo Aug 21 '18 at 04:16

1 Answers1

0
  1. Remove the Range( from the formula... ...DAY(Range(""D"" & i))...
  2. Also you are missing an = sign... "=If(ISNUMBER (DAY...

Other people have already given you some suggestions. Let me explain why you are getting that Application - defined object error.

Your formula

=IF(ISNUMBER(DAY(N2)),DAY(N2),MID(N2,FIND("/",N2,1)+1,FIND("/20",N2,1)-4))&"/"&IF(ISNUMBER(MONTH(N2)),MONTH(N2),MID(N2,1,FIND("/",N2,1)-1))&"/"&IF(ISNUMBER(N2),YEAR(N2),MID(N2,FIND("/20",N2,1)+1,4))

can be written as (In VBA)

"=IF(ISNUMBER(DAY(N" & "2" & _
")),DAY(N" & "2" & _
"),MID(N" & "2" & _
",FIND(""/"",N" & "2" & _
",1)+1,FIND(""/20"",N" & "2" & _
",1)-4))&""/""&IF(ISNUMBER(MONTH(N" & "2" & _
")),MONTH(N" & "2" & _
"),MID(N" & "2" & _
",1,FIND(""/"",N" & "2" & _
",1)-1))&""/""&IF(ISNUMBER(N" & "2" & _
"),YEAR(N" & "2" & _
"),MID(N" & "2" & _
",FIND(""/20"",N" & "2" & _
",1)+1,4))

which can be further adapted to

"=IF(ISNUMBER(DAY(N" & i & _
")),DAY(N" & i & _
"),MID(N" & i & _
",FIND(""/"",N" & i & _
",1)+1,FIND(""/20"",N" & i & _
",1)-4))&""/""&IF(ISNUMBER(MONTH(N" & i & _
")),MONTH(N" & i & _
"),MID(N" & i & _
",1,FIND(""/"",N" & i & _
",1)-1))&""/""&IF(ISNUMBER(N" & i & _
"),YEAR(N" & i & _
"),MID(N" & i & _
",FIND(""/20"",N" & i & _
",1)+1,4))

So you see we do not need the Range() in the formula. Now you can use that in the loop.

Having said that, not need to use a loop. You can enter a formula in a range in one go. See this example

Sub test()
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim sFormula As String

    '~~> Change this to the relevant sheet
    '~~> Stop using `Activesheet`. Active sheet
    '~~> may not be the sheet you think is active
    Set ws = Sheet1

    sFormula = "=IF(ISNUMBER(DAY(N2)),DAY(N2),MID(N2,FIND(""/"",N2,1)" & _
               "+1,FIND(""/20"",N2,1)-4))&""/""&IF(ISNUMBER(MONTH(N2))," & _
               "MONTH(N2),MID(N2,1,FIND(""/"",N2,1)-1))&""/""&IF(" & _
               "ISNUMBER(N2),YEAR(N2),MID(N2,FIND(""/20"",N2,1)+1,4))"
    With ws
        LastRow = .Range("D" & .Rows.Count).End(xlUp).Row

        .Range("B2:B" & LastRow).Formula = sFormula
    End With
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250