Avatar
玉文 楊
12,284 views| 2  Posts

EXCEL - VBA 日期不同格式轉換個人巨集活頁簿

Sub 插入欄()

ActiveCell.Offset(0, 1).Select

Selection.EntireColumn.Insert

Selection.EntireColumn.Insert

ActiveCell.Offset(0, -1).Select

End Sub

Sub 切割()

x = ActiveCell.Address

Selection.TextToColumns Destination:=Range(x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

:="/", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _

TrailingMinusNumbers:=True

End Sub

Sub 執行轉換()

While ActiveCell.Value <> Empty

插入欄

切割

合併

刪除欄

格式轉換

ActiveCell.Offset(1, 0).Select

Wend

End Sub

Sub 刪除欄()

ActiveCell.Offset(0, 1).Select

Selection.EntireColumn.Delete

Selection.EntireColumn.Delete

ActiveCell.Offset(0, -1).Select

End Sub

Sub 合併()

x = ActiveCell.Value

If Int(x) >= 100 Then

x = Int(ActiveCell.Value) + 1911 & "/" & Int(ActiveCell.Offset(0, 1).Value) & "/" & Int(ActiveCell.Offset(0, 2).Value)

ActiveCell.Value = x

Else

x = Int(ActiveCell.Offset(0, 2).Value) + 11 & "/" & Int(ActiveCell.Value) & "/" & Int(ActiveCell.Offset(0, 1).Value)

ActiveCell.Value = x

End If

End Sub

Sub 格式轉換()

x = ActiveCell.Value

Selection.NumberFormatLocal = "G/通用格式"

ActiveCell.FormulaR1C1 = x

Selection.NumberFormatLocal = "[$-404]e""/""mm""/""dd;@"

End Sub

发表者 alex位置在: 上午12:40  

http://alexsir.blogspot.com

about 11 years ago 0 likes  0 comment  0 shares

About

Learn More

Location (City, Country)
Taipei, Taiwan
Gender
male
Member Since
October 8, 2008