Help : Apply formula to selected cells

[anish.ms]

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

[EirikDaude]

To only loop trough visible cells in your range, use:


For Each myCell In myRange.SpecialCells(xlCellTypeVisible)

For the other art of your question, using myCell.Formula instead of myCell.Value will probably help. Though you'll have to remove the equal sign if using the existing formula within another. Something like:


myCell.Formula = "=(" & Replace(myCell.Formula, "=", "", 1, -1, vbBinaryCompare) & ")" & InputFormula

Note that you'll run into trouble if you use an equal sign other places than at the start of the formula using this method, in that case


Right(myCell.Formula, Len(myCell.Formula) - 1)

might be a better choice for what to put into the parentheses. This will however break if there *isn't* an equal sign at the start of the formula. It shouldn't be too hard to foolproof this a bit further, but I'll leave that as an exercise to the reader. I think what I have included here should give you a good starting point at any rate.

[anish.ms]

Thanks EirikDaude (http://www.vbaexpress.com/forum/member.php?24740-EirikDaude)

For the time being, the following code is fine as there wont be an equal sign in other than at the start of the formula



myCell.Formula = "=(" & Replace(myCell.Formula, "=", "", 1, -1, vbBinaryCompare) & ")" & InputFormula