Find 2 values in adjacent columns simultaneously

[BigPig]

Hi All,

I know how to find one value, but I want to find 2 values in two different
ranges. This is what I have so far...

For Each NewCell In NewRng

CheckData = NewCell
Set C =
Worksheets("Unliquidated").Columns("b").Find(what:=CheckData, _
LookIn:=xlValues, lookat:=xlWhole)

If Not C Is Nothing Then
Do lots of this
Else
Do some of that
End If
Next

I am trying to find a two values from a row of data, in another table which
is configured in the same way. I tried using an If statement where "Do lots
of this" is listed,
Worksheets("Unliquidated").Select
C.Select
If ActiveCell.Offset(0, 6).Text <> NewCell.Offset(0, 6).Text
Then
Do some of this
End If
What it did was if the value was found in C, then offset from the value
(0,6) compare it with the newcell.offset....and if it was not equal ..do some
of this.
It kind of worked, however there are multiple instances of the value that
CheckData is looking for, so the above script (just above this para),
evaluates the first row of data it comes across.

Again, I want to find 2 values simultaneously. These 2 values are on the
same row, but in different columns.

I hope you can read through my muddled description. Any help or suggestions
will be greatly appreciated.

Thanks.

 

[Joel]

first, when you returned C with your excel know that the worksheet is already
"Unliquidated". You don't have to specify the worksheet again.

Second there is no need top do the C.Select.

from
If ActiveCell.Offset(0, 6).Text <> NewCell.Offset(0, 6).Text

to
If C.Offset(0, 6).Text <> NewCell.Offset(0, 6).Text

 

[RyanH]

I'm not really sure what you are looking for so I chose the ActiveCell. Hope
this helps.

Sub Find2Values()

Dim CheckData As Range
Dim LastRow As Long
Dim cell As Range
Dim myRange As Range
Dim c As Range

Set CheckData = ActiveCell

LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row

Set myRange = Worksheets("Sheet1").Range("B1:B" & LastRow)

For Each cell In myRange

Set c = myRange.Find(What:=CheckData, LookIn:=xlValues,
LookAt:=xlWhole)

If c Is Nothing Then
'do this
Else
'do some of that
End If

If cell.Text <> cell.Offset(0, 6).Text Then
'Do this
Else
'Do that
End If

Next cell

End Sub

 

[JLGWhiz]

You can't find them simultaneously. You will have to use a FindNext method
to find them consequtively.

 

[BigPig]

Hi All,

Thanks to you all for your codes and for pointing out better ways of coding.
What I ended up doing is concatenating the two columns that I wanted to find
adjacent values. Made that the searchable range and it works.