Macro works but its Slow

Hi all.
First of all I am a beginner and I am sure there are better ways to do every piece of my macro below.
Please see and let me know if you have any suggestions to make it work faster.

Many thanks for your time.

Sub Macro2()
Cells(4, 22) = Cells(4, 1)
Cells(4, 23) = Cells(4, 2)
i = 5
j = 4
Do While Cells(i, 1) = Cells(j, 22) And Cells(i, 2) = Cells(j, 23)
If Cells(i, 19) > Cells(i, 18) Then
Cells(j, 24).Value = "C"
Do While Cells(i, 1) = Cells(j, 22) And Cells(i, 2) = Cells(j, 23)
i = i + 1
Cells(2, 27) = i
Cells(3, 27) = j
Loop
If IsEmpty(Cells(i, 1)) Then
GoTo d:
End If
j = j + 1
Cells(2, 27) = i
Cells(3, 27) = j
Cells(j, 22) = Cells(i, 1)
Cells(j, 23) = Cells(i, 2)
Else:
i = i + 1
Cells(2, 27) = i
Cells(3, 27) = j
If IsEmpty(Cells(i, 1)) Then
GoTo d:
End If
If Cells(i, 1) <> Cells(j, 22) Or Cells(i, 2) <> Cells(j, 23) Then
Cells(j, 24).Value = "UNC"
j = j + 1
Cells(j, 22) = Cells(i, 1)
Cells(j, 23) = Cells(i, 2)
End If
End If
Loop
d:
End Sub

Can you give us a sample data set? Trying to set one up to step through your code is tedious. Also, if you could explain what you are trying to accomplish (if the data set doesn't make that obvious) that would help a lot too.

But just for starters, something like this is processor intensive:

Please Login or Register  to view this content.

You're essentially updating a value in a cell every time through the loop (and I have no idea how many rows/i's you have). Instead, you could just do your incrementation, then put the final value in a cell:

Please Login or Register  to view this content.

If you've got thousands and thousands of rows, that would help.

Anyway, I suspect you're doing some unnecessary looping that is taking a lot of time. Can tell you more with a data set.

I agree with walruseggman. The submitted code is very inefficient. It would be more productive to submit a statement demonstrating what you are trying to accomplish along with a sample workbook and have new code written for it.

Lower Retail G1 670.8 -369.3
Lower Retail G1 670.8 2749.9
White G1 670.8 80.7
White G1 670.8 -145.0
White G1 670.8 113.1
White G1 670.8 -188.4
White G1 670.8 843.9
White G1 670.8 1851.2
White G1 670.8 -809.2
White G1 670.8 -876.3
White G1 670.8 1189.5
White G1 670.8 547.6
White G1 670.8 -506.9
White G1 670.8 996.9
White G1 670.8 799.2
White G1 670.8 1922.3
White G1 670.8 -545.0
White G1 670.8 -799.9
White G1 670.8 1144.7
White G1 670.8 618.7
White G1 670.8 -242.7
White G1 670.8 1151.5
Upper Retail G2 670.8 881.1
Upper Retail G2 670.8 642.8
Upper Retail G2 670.8 1435.0
Upper Retail G2 670.8 1034.2
Upper Retail G2 670.8 1642.3
Upper Retail G2 670.8 -26.4
Here is a sample
First two columns are A and B last two are R and S
Here is a brief description;
for a range with similar values in first two cells in columns a and b, compare rows r and s and if s >r record C otherwise record UNC.
I am creating two columns with unique set of names of the first two columns and placing C or UNC in front of them.
Cells(2, 27) = i
Cells(3, 27) = j
are just useless counters for me to know where I am! ignore them.
Thanks for your time.

Submit an example workbook.

For each value in Column A, starting in Row 4:
If the value in Column A and Column B Row i haven't been seen before, then:
1) Add Column A and B values to first blank row in V & W starting with Row 4
2) Compare R & S Values in Row i, then:
If S > R in Row i, then:
put "C" in Column X in the row with the newly added V & W values
Otherwise
repeat 2 for the next row with same first two cell values.
if non of the rows with same first two cell values S > R then put "UNC" in Column X
repeat this for next set of rows with similar first two cells.


Many thanks

Here you go. If it does do what you want it to, I'd be interested to hear from you how much faster it is.

Please Login or Register  to view this content.