vba - 使用 For Next 循环转换超过 500 行的日期格式

Question

我需要将“C”列中的日期(直到最后一行)从现有格式 24/01/2016 转换为 24.01.2016 结果必须采用日期格式。

我当前的代码是:

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

我得到的答案是 30.12.1899 有点偏离目标

Answer 1

尝试更改 Range.NumberFormat property .

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