PDA

View Full Version : If Statements in VBA



Michael1974
06-29-2015, 11:20 AM
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
06-29-2015, 12:18 PM
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
06-29-2015, 01:05 PM
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
06-29-2015, 02:14 PM
cross posted at http://www.excelguru.ca/forums/showthread.php?4637-If-Statements-in-VBA