Consolidate If

[billandrew]

Evening

Is there a simpler way to perform this action. and possibly center align the offset value without a new loop.

Thanks guys

Dim r As RangeSet r = Range("Z3", Range("Z" & Rows.Count).End(xlUp))
For Each c In r
If c.Value = "AmountPaid" Then c.Offset(, 8) = "Close"
If c.Value = "Amount Paid" Then c.Offset(, 12) = "Close"
If c.Value = "Amount Paid" Then c.Offset(, 16) = "Close"
If c.Value = "Amount Paid" Then c.Offset(, 20) = "Close"


Next c

 

[Rick Rothstein]

How about doing it without a loop...

[table="width: 500"]
[tr]
	[td]On Error GoTo NoAmountPaid
With Range("Z3", Cells(Rows.Count, "Z").End(xlUp))
  .Replace "Amount Paid", "#N/A", xlWhole, , False, , False, False
  Intersect(.SpecialCells(xlConstants, xlErrors).EntireRow, Range("AH:AH,AL:AL,AP:AP,AT:AT")).Value = "Close"
  .Replace "#N/A", "Amount Paid"
End With
NoAmountPaid:[/td]
[/tr]
[/table]

 

[billandrew]

Thank You Mr. Rothstein Works great!

Is the On Error Go To NoAmountPaid is placed to prevent if an error occurs?

 

[Rick Rothstein]

Is the On Error Go To NoAmountPaid is placed to prevent if an error occurs?

It protects against the range not having any cells with the words "Amount Paid" in them. If it is guaranteed that "Amount Paid" will always exist at least once in the range, you can remove the On Error statement and the NoAmountPaid label.

 

[billandrew]

Thank You again. Perfection!