Last active
April 16, 2017 15:30
-
-
Save ateneva/9e6ff22eb984a62026dd7c7fe50b247f to your computer and use it in GitHub Desktop.
Ask user which formulas he/she would like to replace with values
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ZapFormulaValuesUserInput() | |
Dim MyRange As Range | |
Dim Cell As Range | |
Dim prv As Variant | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
With ActiveSheet | |
On Error GoTo handler | |
Set MyRange = Application.InputBox(Prompt:="Please Select a Range", _ | |
Title:="Choose Range to convert to values", Type:=8) | |
For Each Cell In MyRange.SpecialCells(xlCellTypeVisible) | |
If Not IsEmpty(Cell) = True Then | |
prv = Cell.Value | |
If Cell.HasFormula = True Then Cell.Value = prv | |
End If | |
Next Cell | |
handler: MsgBox ("Operation Cancelled or Completed") | |
End With | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment