CDate 可以在没有 for 循环的 Range 上使用吗?
是的possible无需循环即可转换范围,但没有CDATE
Excel 中的公式,因此您必须使用公式Date()
with RIGHT()
, MID()
and LEFT()
例如
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
现在回答你的问题
Try this
Option Explicit
Sub Sample()
Dim rng As Range
Dim sAddr As String
Dim ws As Worksheet
Dim lRow As Long
'~~> Change Sheet as applicable
Set ws = Sheet1
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rng = .Range("A1:A" & lRow)
sAddr = rng.Address
rng = Evaluate("index(DATE(RIGHT(" & sAddr & _
",4),MID(" & sAddr & ",4,2),LEFT(" & _
sAddr & ",2)),)")
'~~> Change to whatever format you want
rng.NumberFormat = "dd/mm/yyyy"
End With
End Sub
解释参见将整个范围转换为大写而不循环遍历所有单元格 https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells
行动中
话虽如此,我更喜欢@Rory上面提到的。使用Data - Text to Columns
.