Formula - Relatively - Absolutely!

With this code the formulas can be set relatively/absolutely - absolutely/relatively within a marked range in a worksheet. The code can be started over the combination of keys CTRL+F2. The files at the end of the article are Excelfiles of the version 2003 and 2007.


The following code belonged in "ThisWorkbook"



Option Explicit
Private Sub Workbook_Open()
Application.OnKey "^{F2}", "Module1.Relative_Absolute"
End Sub
Private Sub Workbook_Deactivate()
Application.OnKey "^{F2}"
End Sub

The following code belonged In "Module1"

Option Explicit
Public Sub Relative_Absolute()
Dim rngRange As Range
Dim rngCell As Range
On Error GoTo Relative_Absolute_Error
Set rngRange = Application.InputBox _
(Prompt:="Mark a range!", Type:=8)
Select Case InStr(ActiveCell.Formula, "$")
Case 0
For Each rngCell In rngRange
rngCell.Formula = Application.ConvertFormula _
(Formula:=rngCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, _
ToAbsolute:=xlAbsolute)
Next rngCell
Case Else
For Each rngCell In rngRange
rngCell.Formula = Application.ConvertFormula _
(Formula:=rngCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, _
ToAbsolute:=xlRelative)
Next rngCell
End Select
Set rngRange = Nothing
On Error GoTo 0
Exit Sub
Relative_Absolute_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
End Sub


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...