Hi All,
Just need some clue as to how to identify the SUM formula using VB. I tried the following, of no use:
[vba]
If Not cell.HasFormula And Not cell.Formula Like "=sum(" Then
[/vba]
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.
Thanx
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).
[vba]
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
Else
cell.Value = vbNullString
End If
Next cell
End Sub
[/vba]
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...
Thanx
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
Else
' Do actions relevant to formulas other than Sum
End If
Else
' Do actions relevant to non-formula cells
End If
Hope this helps.
Regards,
Batman.
Don’t have an account yet? Register yourself now and be a part of our community!