Excel -skift mellem absolutte og relative referencer i et område

Sub SkiftRefType()
    Dim celle As Range
    Dim Reftype As Integer
    Dim Svar As Variant
    Svar = InputBox("Skift alle refencer i markeret område" & vbLf & _
        "Tast ""r"" for relativ eller ""a"" for absolut")
    Select Case LCase(Svar)
    Case "r"
        Reftype = xlRelative
    Case "a"
        Reftype = xlAbsolute
    Case "\"
        Exit Sub
    Case Else
        SkiftRefType
    End Select
    For Each celle In Selection
        celle.Formula = Application.ConvertFormula(Formula:=celle.Formula, _
        FromreferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=Reftype)
    Next celle
End Sub

Har selv fundet en løsning


'Convert_Reference Type Macro
'A Visual Basic module to convert absolute references to relative
'references or relative references to absolute references.

  Sub Conv_RefType()

      Dim Conv As String

      'Prompt user to change to relative or absolute references
      Conv = Application.InputBox _
          ("Type A to convert to Absolute, R to Relative Reference(s)", _
          "Change Cell Reference Type")

      'If changing relative to absolute references
      If UCase(Conv) = "A" Then

          'Loop through each cell selected
          For Each Mycell In Selection

              If Len(Mycell.Formula) > 0 Then

                  'Stores cell's formula as variable
                  MyFormula = Mycell.Formula

                  'Converts formula to absolute reference style
                  NewFormula = Application.ConvertFormula _
                      (Formula:=MyFormula, _
                      fromReferenceStyle:=xlA1, _
                      toReferenceStyle:=xlA1, _
                      toAbsolute:=xlAbsolute)

                  'Replaces old formula with new absolute formula
                  Mycell.Formula = NewFormula

              End If

          Next

      'If changing absolute to relative references
      ElseIf UCase(Conv) = "R" Then

          'Loop through each cell selected
          For Each Mycell In Selection

              If Len(Mycell.Formula) > 0 Then

                  'Stores cell's formula as variable
                  MyFormula = Mycell.Formula

                  'Converts formula to relative reference style
                  NewFormula = Application.ConvertFormula _
                      (Formula:=MyFormula, _
                      fromReferenceStyle:=xlA1, _
                      toReferenceStyle:=xlA1, _
                      toAbsolute:=xlRelative)

                  'Replaces old formula with new relative formula
                  Mycell.Formula = NewFormula

              End If

          Next

      'Display Error message if choice entered is invalid
      ElseIf UCase(Conv) <> "FALSE" Then

          MyMsg = "Enter A for Absolute, R for Relative Reference(s)"
          MyTitle = "Option Not Valid"
          MyBox = MsgBox(MyMsg, 0, MyTitle)

      End If

  End Sub

er løst