If Statements in VBA

[Michael1974]

I have the attached spreadsheet where I am trying to use the if Statement based on the following:

- From Column I: If Column I is "NO" then Column I="No interval found" for each row of the spreadsheet and Column J,K,L must be blank (" ")

- Or From the the same Column I: If Column I is "PDM" then Column I= "PDM (but No interval found)" and Column J,K,L must be blank (" ")


Please see the tab called "Results Desired" to understand what I am talking about.

Thanks in advance

Michael

[SamT]

There are many ways to accomplish this, but I like to use methods that anyone can understand and maintain and modify.

Put this code in a standard module. With the sheet open that you want to run it on, use the Excel Menu >> Tools >> Macros to select this sub, then click "Run"

Option Explicit

Sub Replace_NO_PDM()
Dim Cel As Range
Dim ColumnI As Range 'That is Column + uppercase "Eye," not Column + "ell."
Dim LastCel As Range

Const strNO As String = "No interval found"
Const strPDM As String = "PDM (but No interval found)"
Const ResizeCount As Long = 4 'Clears Column M, set to 3 to omit Column M

With ActiveSheet
  Set LastCel = .Cells(Rows.Count, "I").End(xlUp)
  Set ColumnI = Range(Range("I2"), LastCel)
  
  For Each Cel In ColumnI
    If UCase(Cel.Value) = "NO" Then
      Cel.Value = strNO
      Cel.Offset(0, 1).Resize(1, ResizeCount).ClearContents
    ElseIf UCase(Cel.Value) = "PDM" Then
      Cel.Value = strPDM
      Cel.Offset(0, 1).Resize(1, ResizeCount).ClearContents
    End If
  Next Cel
  ColumnI.Columns.AutoFit
End With
    
End Sub

[snb]

Sub M_snb()
    With Sheets("snb")
        .Cells.UnMerge
        .Columns(9).Replace "no", "no interval found", 1
        .Columns(9).Replace "PDM", "PDM (But no interval found}", 1

        For j = 10 To 13
          .Columns(j).Replace Choose(j - 9, "inter", "val", "found!"), "", 1
          .Columns(j).Replace Choose(j - 9, "(But", "no", "interv", "al found!)"), "", 1
        Next
    End With
End Sub

[p45cal]

cross posted at http://www.excelguru.ca/forums/showt...tements-in-VBA