need help with my code

[kiwis]

Hi

Need some help with my code,

My code will change the date (dd/mm/yyyy) to this format mm/dd/yyyy
format.

part of my code

iLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To iLastRow
With ws.Cells(i, "B")

.Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2),
MID(.value, SEARCH(" / ",.value)+1,SEARCH(" / ", MID(.value,
SEARCH(" / ",.value)+1, 10))-1),LEFT(.value, SEARCH(" /
",.value)-1)),.value)"


End With
Next i


i can do it in excel the formula
= IF(ISTEXT(B4), DATE(RIGHT(B4, 2),MID(B4,
SEARCH("/",B4)+1,SEARCH("/", MID(B4, SEARCH("/",B4)+1,
10))-1),LEFT(B4, SEARCH("/",B4)-1)),B4)

but when i put it in a macro, i get error "Type mismatch"

how do i set it such that it will loop through all the cells from in
the for loop?

Thank you
I have send a few days trying to fix this date problem

rgds
kiwis

 

[Guest]

I think your issue is quotes - if you want to use quotes within a quoted
string, then you have to use two quotes, eg:

"my string ""with quoted subsstring"""

 

[kiwis]

Hi Ben

i have tried your suggestion

..Formula = "=IF(ISTEXT(.value), DATE(RIGHT(.value, 2), MID(.value,
SEARCH("" / "",.value)+1,SEARCH("" / "", MID(.value, SEARCH("" /
"",.value)+1, 10))-1),LEFT(.value, SEARCH("" / "",.value)-1)),.value)"

it is still giving me error,
application defined error or object defined error.

Anyone can help?

Thanks

rgds
kiwis

 

[NickHK]

Yes, because you have to construct the string correctly, concatenating the
part the parts that have to be evaluated.
e.g. ".value" is not part of your string, as it has to be evaluated and THEN
added to the string.
..Formula = "=IF(ISTEXT(" & .value & "), DATE(RIGHT(" & .value & ", 2),....
etc

Do you have to do this with a formula in VBA ?

There are easier ways of dealing with dates. e.g.
DateSerial, DateValue, Format etc

NickHK

 

[Guest]

Sub AA()
Dim ws As Worksheet
Dim ilastrow As Long
Dim i As Long
Set ws = ActiveSheet
ilastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To ilastrow
With ws.Cells(i, "B")
.Offset(0, 1).Formula = "=IF(ISTEXT(" & _
.Address & "),DATE(RIGHT(" & _
.Address & ",2),MID(" & .Address & ",SEARCH(""/""," & _
.Address & ")+1,SEARCH(""/"", MID(" & .Address & _
",SEARCH(""/""," & .Address & ")+1,10))-1),LEFT(" & _
.Address & ",SEARCH(""/""," & .Address & _
")-1))," & .Address & ")"
End With
Next i

End Sub

produced the same formula you show in your example. (it places the formula
in column C in the corresponding row. )

To do that, you need to use Address instead of Value. Plus the suggestions
of the other posters.

 

[kiwis]

Thank you everyone for their reply,

NickHK - i have tried to set the format to what i want but to no
sucess, so i have to write code to split up the
date & then regroup into what i want mm/dd/yyyy. The orginal date was
copied from a raw CSV file which is having dd/mm/yyyy format.

Tom - thanks, the code work but the year is giving me 1903 instead of
2003. when i type the formula into the cell, i get the correct year
2003 but when i run the macro AA, the year becomes 1903, why is that
so? any answer to why the year is wrong when using the marco but
correct when i type into the cell?

Thank you

 

[kiwis]

i found the problem, the string "30/12/2005 " have a space which is
causing it to return
year 1905 instead of 2005.

solved the problem