Excel 常用 VBA

VBA - Excel 切换工作表

通过 VBA 实现用 Ctrl-Tab 和 Ctrl-Shift-Tab 来切换工作表的功能，可能用得上。

Sub Auto_Open()
    Application.OnKey "^{tab}", "DownSheet"
    Application.OnKey "^+{tab}", "UpSheet"
End Sub
Sub DownSheet()
    Dim i As Integer
    i = Worksheets.Count
    If ActiveSheet.Index < i Then
        Worksheets(ActiveSheet.Index + 1).Activate
  Else
        Worksheets(1).Activate
    End If
End Sub
Sub UpSheet()
    Dim i As Integer
    i = Worksheets.Count
    If ActiveSheet.Index > 1 Then
        Worksheets(ActiveSheet.Index - 1).Activate
    Else
        Worksheets(i).Activate
    End If
End Sub

VBA - Excel 清除 R1C1 格式

有些时候遇到的数据是 R1C1 格式的，用这个宏来整理一下

Sub RCFix()
Dim rng As Range
For Each rng In Intersect(Selection, ActiveSheet.UsedRange)
    With rng
        .FormulaR1C1 = .Value
        .NumberFormat = "General"
    End With
Next
End Sub