你好fvflove兄,我的a列记载着这样格式的数据:20070707,20070708,20070709等等,我想改成诸如2007-07-07格式的,因此将你上个帖子写给我的批量改数据的语句改了一下:Sub Macro1()
Dim xSheet As Worksheet
Dim Index As Integer
Set xSheet = ThisWorkbook.Worksheets(1)
Index = 1
While Len(xSheet.Range("$a$" & Index).Text) = 8
xSheet.Range("$a$" & Index).NumberFormat = "@"
xSheet.Range("$a$" & Index).Value = Mid(xSheet.Range("$a$" & Index).Value, 3, 2) & "-" & Mid(xSheet.Range("$a$" & Index).Value, 5, 2) & "-" & Right(xSheet.Range("$a$" & Index).Value, 2)
Index = Index + 1
Wend
End Sub但是更改之后,excel无法将2007-07-07识别为日期,怎么改?
Dim xSheet As Worksheet
Dim Index As Integer
Set xSheet = ThisWorkbook.Worksheets(1)
Index = 1
While Len(xSheet.Range("$a$" & Index).Text) = 8
xSheet.Range("$a$" & Index).NumberFormat = "@"
xSheet.Range("$a$" & Index).Value = Mid(xSheet.Range("$a$" & Index).Value, 3, 2) & "-" & Mid(xSheet.Range("$a$" & Index).Value, 5, 2) & "-" & Right(xSheet.Range("$a$" & Index).Value, 2)
Index = Index + 1
Wend
End Sub但是更改之后,excel无法将2007-07-07识别为日期,怎么改?
Sub Macro1()
Dim xSheet As Worksheet
Dim Index As Integer
Set xSheet = ThisWorkbook.Worksheets(1)
Index = 1
While Len(xSheet.Range("$a$" & Index).Text) = 8
xSheet.Range("$a$" & Index).Value = Mid(xSheet.Range("$a$" & Index).Value, 3, 2) & "-" & Mid(xSheet.Range("$a$" & Index).Value, 5, 2) & "-" & Right(xSheet.Range("$a$" & Index).Value, 2)
Index = Index + 1
Wend
End Sub
Sub Macro1()
Dim xSheet As Worksheet
Dim Index As Integer
Set xSheet = ThisWorkbook.Worksheets(1)
Index = 1
While Len(xSheet.Range("$a$" & Index).Text) = 8
xSheet.Range("$a$" & Index).NumberFormat = "yy-mm-dd" ' "yyyy-mm-dd" 表示年份用四位
xSheet.Range("$a$" & Index).Value = Mid(xSheet.Range("$a$" & Index).Value, 3, 2) & "-" & Mid(xSheet.Range("$a$" & Index).Value, 5, 2) & "-" & Right(xSheet.Range("$a$" & Index).Value, 2)
Index = Index + 1
Wend
End Sub
fvflove兄,你把xSheet.Range("$a$" & Index).NumberFormat = "@" 去处了,这样更改之后格式是被识别成日期了,但是年份全错了,原来的2007年变成1930,2087什么的都有,你可以拿些数据做做试验,怎么回事啊。。
'晕 我对Excel的数据格式要好好去补一下了.
'这样应该没有问题了吧.
Sub Macro1()
Dim xSheet As Worksheet
Dim Index As Integer
Set xSheet = ThisWorkbook.Worksheets(1)
Index = 1
Dim A1 As Date
While Len(xSheet.Range("$a$" & Index).Text) = 8
xSheet.Range("$a$" & Index).Value = Mid(xSheet.Range("$a$" & Index).Value, 5, 2) & "-" & Right(xSheet.Range("$a$" & Index).Value, 2) & "-" & Mid(xSheet.Range("$a$" & Index).Value, 3, 2)
Index = Index + 1
Wend
End Sub
我运行了之后跳出一个对话框,“运行时错误‘6’ 溢出”,点击确定,a1单元格被#号充满了,然后a2a3等等下面的单元格没有变化。是不是哪里有错误?顺便给我也解释解释错误原因吧:D 谢谢!