Identify SUM formula using vb code

  • Hi All,

    Just need some clue as to how to identify the SUM formula using VB. I tried the following, of no use:

    If Not cell.HasFormula And Not cell.Formula Like "=sum(" Then

    The idea being, I want to retain the SUM formula in a range of cells and delete if it has some text or other formulas.

    Any help would be appreciated.


  • Re: Identify SUM formula using vb code

    Hi Pradeep,

    I don't entirely understand what your objective is. Anyway, does this help:
    [vba]Sub Test()
    Dim rngCell As Range

    For Each rngCell In Sheet1.Range("A1:A30").SpecialCells(xlCellTypeFormulas)
    If Left(rngCell.Formula, 5) = "=SUM(" Then
    MsgBox "Found SUM at " & rngCell.Address
    End If
    Next rngCell

    End Sub[/vba]

  • Re: Identify SUM formula using vb code

    Hi Richie,

    Thanx for the hint. It works.

    Following is the code(just to let you know what I wanted and to let someone use if they also in need of the same).

    Option Explicit
    Sub try()
    Dim rn As Range
    Dim cell As Range
    Set rn = Selection
    For Each cell In rn
    If cell.HasFormula And Left(cell.Formula, 5) = "=SUM(" Then
    cell.Formula = cell.Formula
    cell.Value = vbNullString
    End If
    Next cell
    End Sub

    I am trying to keep the format with Sum formulas deleting any other texts/numbers etc., so that I can enter the data for the next month...


  • Re: Identify SUM formula using vb code

    Hi Pradeep,

    Richie's suggestion will work if you only want to action cells with formulas, but I think you need to check all cells in the range.

    Your original code has a logic error in that the two parts of the If statement have conflicting conditions that can never both be True for the If statement to operate. You need to expand your If statement to two nested If .. Then. .. Else .. statements, as in:

    If cell.HasFormula Then
        If UCase(Left(cell.Formula, 5)) = "=SUM(" Then
            ' Do actions relevant to Sum formulas
            ' Do actions relevant to formulas other than Sum
        End If
        ' Do actions relevant to non-formula cells
    End If

    Hope this helps.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!