if statement with or doesnt work

mikezx10 · #1 (**permalink**) 09-08-10, 10:22

I would like to clear the contents of data entry cells these cells would not have "+" or "=sum" in the formula. In my if statement i tired using or, but it is not working:

If Left(ActiveCell.Formula, 4) <> "=Sum" Or InStr(ActiveCell.Formula, "+") = 0 Then
Selection.ClearContents
End if

Colin Legg · #2 (**permalink**) 09-08-10, 10:39

Hi,

I think it should be a logical AND, not OR.

If the cell does not contain "+" and it does not start with "=SUM" then clear the contents.

Code:

Sub foo()
    Dim rngCell As Range
 
    For Each rngCell In Range("A1:A10").Cells
 
        'make sure the cell has a formula
        If rngCell.HasFormula Then
 
            'make sure it does not contain a +
            If VBA.InStr$(1, rngCell.Formula, "+", vbTextCompare) = 0 Then
 
                'make sure it does not begin with a SUM function
                If VBA.Left$(rngCell.Formula, 4) <> "=SUM" Then
 
                    rngCell.ClearContents
 
                End If
 
            End If
 
        End If
 
    Next rngCell
 
End Sub

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech