Loop to compare Cells in a Column

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Do you know the 1,3, 10000 ahead of time, or you are just searching the list for unique items?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top