CODEWITHSUNDEEP
vbaexcelnumber-formatting
Zakky
Zakky

Reputation: 59

convert a date format for over 500 rows using a For Next Loop

I need to convert dates (up to last row) in column "C" from the existing format 24/01/2016 to 24.01.2016 The result has to be in date format.

My current code is:

LastRow9 = ws5.Cells(Rows.Count, "C").End(xlUp).Row
For X9 = 1 To LastRow9
searchvalue = Cells(X9, "C").Value
Answer = Split(searchvalue, "/")
ws5.Cells(X9, "A").Value = Answer
ws5.Cells(X9, "A").Value = Format(Answer, "dd.mm.yyyy")
Next X9

the answer i get is 30.12.1899 a bit off the mark

Upvotes: 2

Views: 77

Answers (1)

user4039065
user4039065

Reputation:

Try changing the Range.NumberFormat property.

with ws5
    .range(.cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp)).NumberFormat = "dd.mm.yyyy"
end with

Upvotes: 2

Related Questions