Consolidate If

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
Evening

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

Thanks guys :)

Code:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
How about doing it without a loop...
Code:
[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]
 
Upvote 0
Thank You Mr. Rothstein Works great!

Is the On Error Go To NoAmountPaid is placed to prevent if an error occurs?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top