Consulting

Results 1 to 3 of 3

Thread: Help : Apply formula to selected cells

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Help : Apply formula to selected cells

    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

  2. #2
    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.

  3. #3
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •