Is it possible to find the first value in a range that is not equal to a
value? I mean, aside from iterating through all the cells.

I have a data set where there might be thousands of lines with a column
value that rarely changes.

What I would like, is to get the first value in a column, then search that
column for the first occurance of a different value.

Is there a way to do it with the Find method of the Range object?

For Example:

Dim rngColumnHeader as Range,rngSearchRange as Range
Dim lngPowerSetting as Long, rngDifferent as Range

'Look for my column header
Set rngColumnHeader = ActiveSheet.Find(What:="Power Setting",
LookIn:=xlValues, LookAt:=xlWhole)

If Not rngColumnHeader is Nothing Then
'If I find it, get the range to search
Set rngSearchRange =
ActiveSheet.Range(rngColumnHeader.Offset(1,0),rngC olumnHeader.End(xlDown))

lngPowerSetting = rngSearchRange.Cells(1).Value

Set rngDifferent = rngSearchRange.Find(What:= ?,LookIn:=xlValues,
LookAt:=xlWhole)

'What do I put in for the question mark to signify a value other than
lngPowerSetting? If I do get a hit, I need to grab other values in the same
row.

Thanks,

Bob

P.S. My values are not sorted, and can go up or down. The test data set I
have to work with has 80 columns with 9000 rows and about 20 columns I need
to do this to. The Power Setting column in my test data set has about 3000
rows of 20, 500 rows of 50, 3000 rows of 20, and the rest 10.

The find method requires a positive match. In other words, you cannot set it
to look for something that is not there. To identify unequal values, you
would probably need to use a loop and store the results somewhere, possibly
in an array or a concatenated variable. I can't think of a way to do it with
the find method.

"INTP56" wrote:

Is it possible to find the first value in a range that is not equal to a
value? I mean, aside from iterating through all the cells.

I have a data set where there might be thousands of lines with a column
value that rarely changes.

What I would like, is to get the first value in a column, then search that
column for the first occurance of a different value.

Is there a way to do it with the Find method of the Range object?

For Example:

Dim rngColumnHeader as Range,rngSearchRange as Range
Dim lngPowerSetting as Long, rngDifferent as Range

'Look for my column header
Set rngColumnHeader = ActiveSheet.Find(What:="Power Setting",
LookIn:=xlValues, LookAt:=xlWhole)

If Not rngColumnHeader is Nothing Then
'If I find it, get the range to search
Set rngSearchRange =
ActiveSheet.Range(rngColumnHeader.Offset(1,0),rngC olumnHeader.End(xlDown))

lngPowerSetting = rngSearchRange.Cells(1).Value

Set rngDifferent = rngSearchRange.Find(What:= ?,LookIn:=xlValues,
LookAt:=xlWhole)

'What do I put in for the question mark to signify a value other than
lngPowerSetting? If I do get a hit, I need to grab other values in the same
row.

Thanks,

Bob

P.S. My values are not sorted, and can go up or down. The test data set I
have to work with has 80 columns with 9000 rows and about 20 columns I need
to do this to. The Power Setting column in my test data set has about 3000
rows of 20, 500 rows of 50, 3000 rows of 20, and the rest 10.

Hi,

The message header and text are contradictory but in either case if your
looking for a value that matches/doesn't match there's no alternative to
iteration. Inbuilt worksheet functions do it (vlookup) even if as users were
not aware of the process and any routine written in VB will do the same it's
simply a matter of finding the most effecient and the answer to that depends
on what your doing.

As an example this is virtually instantaneous for 1000's of rows and dozens
of columns

Cells.Find(What:="4676", After:=ActiveCell).Activate



Mike

"INTP56" wrote:

Is it possible to find the first value in a range that is not equal to a
value? I mean, aside from iterating through all the cells.

I have a data set where there might be thousands of lines with a column
value that rarely changes.

What I would like, is to get the first value in a column, then search that
column for the first occurance of a different value.

Is there a way to do it with the Find method of the Range object?

For Example:

Dim rngColumnHeader as Range,rngSearchRange as Range
Dim lngPowerSetting as Long, rngDifferent as Range

'Look for my column header
Set rngColumnHeader = ActiveSheet.Find(What:="Power Setting",
LookIn:=xlValues, LookAt:=xlWhole)

If Not rngColumnHeader is Nothing Then
'If I find it, get the range to search
Set rngSearchRange =
ActiveSheet.Range(rngColumnHeader.Offset(1,0),rngC olumnHeader.End(xlDown))

lngPowerSetting = rngSearchRange.Cells(1).Value

Set rngDifferent = rngSearchRange.Find(What:= ?,LookIn:=xlValues,
LookAt:=xlWhole)

'What do I put in for the question mark to signify a value other than
lngPowerSetting? If I do get a hit, I need to grab other values in the same
row.

Thanks,

Bob

P.S. My values are not sorted, and can go up or down. The test data set I
have to work with has 80 columns with 9000 rows and about 20 columns I need
to do this to. The Power Setting column in my test data set has about 3000
rows of 20, 500 rows of 50, 3000 rows of 20, and the rest 10.

Thanks for your comments.

At present, I see two ways to do it.

Read each column in as a variant, and iterate through that instead of
constantly hitting the worksheet. This has a limit to how many cells I can do
at one time. Not sure what it is, but I've hit it in the past.

Add an extra column that checks to see if a value in a column is equal to
the value above it. Using INDIRECT, I can put a column number in a cell and
search over the same range for each column, looking for False.

"=AND(INDIRECT(CONCATENATE(""RC"" &
R1C),FALSE)=INDIRECT(CONCATENATE(""R[-1]C"" & R1C),FALSE))"

This formula looks at the top row of it's column to determine which column I
will test. After I find all the False's for a column, I change the number at
the top to look at a different column and search again for False's. It's not
as fast as I would like, about 5 seconds, as it takes a finite amount of time
to update the column when the top value changes. That's why I was hoping I
could use Find.

After I'm done, I delete the column.