K
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
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