anish.ms
12-03-2020, 12:37 AM
Hi,
My code below while applying the input formula in selection, converts the existing value to a result. How to avoid that ?
If I change myCell.Value to myCell.Formula, it is resulting in error
For example in one cell I have a value of =10+20 and then if I apply the code with an input formula of *10. The result will be =(30)*10
But I need the result to be like =(10+20)*10
Also how to exclude the hidden rows and columns while running the code
Option Explicit
Sub ApplyFormula()
Dim myRange As Range
Dim myCell As Range
Dim InputFormula As String
Select Case MsgBox("You Can't Undo This Action. " & "Save Workbook First?", vbYesNoCancel, "Alert")
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set myRange = Selection
InputFormula = InputBox("Enter formula to apply")
Application.ScreenUpdating = False
For Each myCell In myRange
If Not IsEmpty(myCell) Then
myCell.Value = "=(" & (myCell.Value) & ")" & InputFormula
End If
Next myCell
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help
My code below while applying the input formula in selection, converts the existing value to a result. How to avoid that ?
If I change myCell.Value to myCell.Formula, it is resulting in error
For example in one cell I have a value of =10+20 and then if I apply the code with an input formula of *10. The result will be =(30)*10
But I need the result to be like =(10+20)*10
Also how to exclude the hidden rows and columns while running the code
Option Explicit
Sub ApplyFormula()
Dim myRange As Range
Dim myCell As Range
Dim InputFormula As String
Select Case MsgBox("You Can't Undo This Action. " & "Save Workbook First?", vbYesNoCancel, "Alert")
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
Set myRange = Selection
InputFormula = InputBox("Enter formula to apply")
Application.ScreenUpdating = False
For Each myCell In myRange
If Not IsEmpty(myCell) Then
myCell.Value = "=(" & (myCell.Value) & ")" & InputFormula
End If
Next myCell
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help