AW: Fixierung von Zellen
Zellen
Hallo Tim!
Hiermit änderst du Bezüge wie du lustig bist:
Markiere den Bereich und los geht's ;-))
Grüße,
Jens
Sub Bezugsarten_ändern()
Dim c As Range
Dim i As String
i = InputBox(prompt:="Geben Sie bitte die gewünschte Bezugsart ein:" & Chr(10) & _
"1 = relativ, 2 = absolut," & Chr(10) & _
"3 = Spalte absolut / Zeile relativ, 4 = Spalte relativ /Zeile absolut", Title:="Änderung der Formel-Bezugsart", _
Default:="2")
ActiveSheet.Unprotect
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
Select Case i
Case Is = 1
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
End If
Next
Case Is = 2
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
End If
Next
Case Is = 3
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
End If
Next
Case Is = 4
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
End If
Next
End Select
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
End With
'ActiveSheet.Protect
End Sub