Macro to convert formula to value - Excel for Mac 2011

[Frank Horatio]

Hi. New to Macros... Using Excel for Mac 2011.

My goal is to be able to select cells and convert cells with formulas into the resulting values. I manually recorded this Macro:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Option+Cmd+q
'
    Range("D16").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
End Sub

And it works nicely. However, I want to be able to perform this task for many cells. I found this macro, but I am getting a syntax error and don't know how to fix it:

Sub ValuesOnly( )

Dim rRange As Range

    On Error Resume Next

        Set rRange = Application.InputBox(Prompt:="Select the formulas", _

                                  Title:="VALUES ONLY", Type:=8)

        If rRange Is Nothing Then Exit Sub

    rRange = rRange.Value

End Sub</pre>

Thanks!

 

[shg]

The blank line is causing the error:

Sub ValuesOnly()
    Dim rRange      As Range
 
    On Error Resume Next
    Set rRange = Application.InputBox(Prompt:="Select the formulas", _
                                      Title:="VALUES ONLY", Type:=8)
    If rRange Is Nothing Then Exit Sub
 
    rRange.Value = rRange.Value
End Sub

 

[neadbecker]

a

 

[Frank Horatio]

Perfect. Thanks.