VBA to perform multiple cell amends??

[Snowy_30]

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>

 

[mumps]

Hi Snowy. Try this:

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

 

[Snowy_30]

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>

 

[mumps]

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.

 

[Snowy_30]

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?

 

[mumps]

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.