# Loop to compare Cells in a Column

#### KORKIS2

##### Board Regular
So I want to go down a column and compare cells.
I know alot of C programming but not much VBA.

So here is a flow of what I want.
Lets Say

Basically keep looping and working down the Column until the next spot it larger than previous then exit the loop.

P= blank array
X= Cell Value A1
Y= Cell A2

IF X <Y Then
Y stored in P exit
Else Y+1 =Y(Meaning A3)
Return to IF

Basically keep looping and working down the Column until the next spot it larger than previous then exit the loop.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
You didn't specify whether the column was a known size or not. Here's one way assuming you know the column size:

Code:
``````Dim rCell as Range

For Each rCell in Sheet1.Range("A1:A10")
if rCell.Offset(1,0) > rCell Then
rCell.Offset(1,0).Select
Exit For
End If
Next rCell``````

I appreciate you getting back to me so quickly.
The column is not a known size.

In that case you have to figure out the column size first, and there's like 5 different ways to do that, all depending on how your column is laid out and what the values are.

here's one way:

Code:
``````Dim rCell as Range
Dim lLastRow as Long

lLastRow = Sheet1.Range("A:A").End(xldown).Row

For Each rCell in Sheet1.Range("A1:A" & lLastRow)
if rCell.Offset(1,0) > rCell Then
rCell.Offset(1,0).Select
Exit For
End If
Next rCell``````

You'll want to do some reading on how to find the last used cell in a column, it can be trickier than it sounds (ie do you look for blanks, or what about formulas that return blank values)

ADVERTISEMENT
Well I'm searching down a column for that exact reason all the numbers are in numerical order so but I need to know the ranges
1
1
1
1
1
1
2
2
2
2

Therefore by comparing them as one goes down the columns you could find the ranges of rows that 1 is in.

So you want the start/stop row of all the "1s" and same for all the "2s" and so on?

When does it end? At the first blank row? Or do you have a pre-determined list of numbers to search for, like 1-7 or something?

What cell does the list start in?

ADVERTISEMENT
The cell list can start from anything from anything from 1-100,000,000 and go to 100,000,00. I have code that puts the whole column in numerical order and then the code we were talking about writing
is supposed to search down that column and find the ranges of what each number is so
1
1
1
3
3
3
10,000
10,000
10,000
10,000

1 is from 1-3
3 is from 4-6
10,000 is from 7-11

Do you know the 1,3, 10000 ahead of time, or you are just searching the list for unique items?

No it can be any number thats why i was searching for the change because they are going to be in numerical order there for as it moves down the list if it is less than the next number the numbers have changed if it is equal to it should check the next cell

If it will suit your needs, you could just do this in a pivot table.

Call column A "Metric", and call column B "RowNum"
In B2, add this formula and autofill it down the entire column: =Row()

Now create a pivot table from these two columns.

Put "Metric" in the row column, and put Min(RowNum) and Max(RowNum) in the Values section, then show those in the columns as well.

Make sense?

Replies
27
Views
531
Replies
13
Views
355
Replies
1
Views
574
Replies
3
Views
206
Replies
0
Views
642

Threads
1,196,178
Messages
6,013,881
Members
441,791
Latest member
SKeulder

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

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