Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: if statement with or doesnt work

    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

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

Posting Permissions

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