Help with IF statement

mp7

New Member
Joined
Apr 7, 2011
Messages
2
Hi there,

I am a total novice at excel and wanted to get some advice/help.

Basically, I have a 4 columns of data with about 75 rows. In column D, the values vary from 0 to 500000. I want to create a macro/code which will do the following:

1. Sort the data in column C (not D) by size with largest at the top.
2. I want to then work my way up from the bottom so that the number in the last cell is compared with the one above it. If the one above is smaller it is kept but if it is bigger, the whole row will be deleted. This process should be repeated for each cell until the values in column D decrease from largest at the bottom to smallest at the top.
3. I want to then subtract the top cell (not including the heading) from the one below it - ie c2-c3, then c3-c5, etc. and place the result in column E.
4. I want to then subtract D3-2, D4-D3 etc and place the result in column F.
5. I want to divide column F by column E and place the result in column G

I am not sure if this is clear or possible. Any help would be appreciated.

Thanks.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

stnkynts

Board Regular
Joined
Oct 7, 2010
Messages
198
I think i got it here. I assumed that there is a header in row 1. Also this macro has Sheet 1 named as the default Sheet1. If your sheet 1 is named something else just change it in the parenthesis of the code line:
Code:
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")

Here is whole code:
Code:
Option Explicit
Sub MainMacro()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim lastrow As Long, icell As Long
lastrow = ws1.Range("D" & Rows.Count).End(xlUp).Row
'sorting
With ws1.Sort
        .SortFields.Add Key:=Range("C2:C" & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlDescending
        .SetRange Range("A1:D" & lastrow)
        .Orientation = xlTopToBottom
        .Apply
End With
'sorting/deleting rows in column D
For icell = lastrow To 3 Step -1
Repeat:
    If ws1.Range("D" & icell).Offset(-1, 0).Value > ws1.Range("D" & icell).Value Then
        ws1.Range("D" & icell).Offset(-1, 0).EntireRow.Delete Shift:=xlUp
        GoTo Repeat
    End If
Next icell
'Formulas
lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E3").Formula = "=C2 - C3"
Range("E3:E" & lastrow).FillDown
Range("F3").Formula = "=D3 - D2"
Range("F3:F" & lastrow).FillDown
Range("G3").Formula = "=F3 / E3"
Range("G3:G" & lastrow).FillDown
End Sub
 

mp7

New Member
Joined
Apr 7, 2011
Messages
2
Thanks for the help. I ran the code you posted but it seems to work its way up the numbers but seems to eventually delete all the values.

Should I post some examples of the spreadsheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,204
Messages
5,594,826
Members
413,942
Latest member
Dhornsby21

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
Top