# VBA to perform multiple cell amends??

#### Snowy_30

##### New Member
Is it possible to create VBA to scoll through a table of data, example below (actual table is 39k rows long), and where the reason (differential Reason 1 & 2) equals 'Let Property' to remove the differential rate from the differential's 1 and/or 2, total differential and Final rate columns.

Line 1 would change in the table to
Total Differential = null/blank
Differential 1 = null/blank
Final Rate = 2.25, line 2 would be unafected as no let property reason shown
 Total Differential Differential 1 Differential Reason 1 Differential 2 Differential Reason 2 Final Rate 1.00 1.00 Let Property 3.25 1.25 1.25 Other 1.75 2.49 1.99 Other 0.50 Let Property 2.99 1.75 0.50 Let Property 1.25 Other 2.25 1.99 1.99 Other 4.99 1.00 1.00 Let Property 3.50 2.00 1.25 Other 0.75 Other 2.50 1.99 1.99 Other 4.49 0.50 0.50 Let Property 2.75

<TBODY>
</TBODY>

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi Snowy. Try this:
Code:
``````Sub Test()
Application.ScreenUpdating = False
Dim bottomC As Integer
bottomC = Range("C" & Rows.Count).End(xlUp).Row
Dim x As Long
For x = bottomC To 2 Step -1
If Cells(x, 3) = "Let Property" And Cells(x, 5) = "Let Property" Then
Cells(x, 1).Resize(, 2).ClearContents
Cells(x, 4).ClearContents
Cells(x, 6).ClearContents
End If
Next x
Application.ScreenUpdating = True
End Sub``````

Close to what I was after but badly explained so not working as I would want just yet

I didn't mean both reasons had to be Let Property, only 1 of 2 would be for a Let Property reason.

This is how i would expect the first 3 lines to change

 Total Differential Differential 1 Differential Reason 1 Differential 2 Differential Reason 2 Final Rate 2.25 1.25 1.25 Other 1.75 1.99 1.99 Other 2.49

<TBODY>
</TBODY>

My apologies, Snowy. I'm still a little confused as to what you want to do. Could you please describe step by step what you want to happen.

Sorry, its difficult to explain.

The idea is to remove all the rates and reasons related to 'let property' to reflect how each cell would look if no let property rate existed.

You have 6 columns of data
1) Total Differential (Rate % Diff 1 + Diff 2)
2) Differential 1 (Rate %)
3) Differential Reason 1 (Reason for Differential 1 Rate)
4) Differential 2 (Rate %)
5) Differential Reason 2 (Reason for Differential 2 Rate)
6) Final Rate (This is the Rate % being charged)

Example 1
1) Total Differential = 2.49% (This would reduce by 0.50 to 1.99%)
2) Differential 1 = 1.99 (This would remian unchanged as not 'let property reason)
3) Differential Reason 1 = Other (This would remian unchanged as not 'let property reason)
4) Differential 2 = 0.50 (This would be 0/Null as Let Property reason so must be removed)
5) Differential Reason 2 = Let Property (This would be 0/Null as Let Property reason so must be removed)
6) Final Rate = 2.99 (This would reduce by 0.50 to 2.49% to reflect Let Property rate being removed)

Does this help?

That helps explain one example but there are many possible combinations. It's still hard for me to follow. It must be old age!!!! Could you post a chart similar to the one in your first post, showing what the data for all the examples would look like after the changes are made? Maybe I'll be able to figure it out by looking at all the examples.

Replies
13
Views
650
Replies
3
Views
315
Replies
1
Views
585
Replies
0
Views
1K
Replies
1
Views
91

1,196,501
Messages
6,015,563
Members
441,901
Latest member
joshtfin

### 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.

### Which adblocker are you using?

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

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