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