find TWO values in a worksheet
 

I have two worksheets. One called "OpenOrders" and one called
"Supplier". I need to determine if the supplier id in the Supplier
worksheet matches the id in the OpenOrders AND if the order dates in both
match. It seems so simple, but nothing I'm trying is working.

The following DOES find the ID, but I can't pick up the order date in the
ID's row so I can compare it with the order date in supplier. After I found
the ID, I tried picking up the date using:
OrdersOrderDate = Cells(FoundRow, 3) ', e.g., foundrow=2
This didn't work. I then tried the following (which also doesn't work)

Your help would be GREATLY appreciated!

Dim FoundRow As String
Dim C As Range
Dim D As Range

With Sheets("Supplier").Range("b1:f2000")
ID = Cells(FromRow, 2) ' get ID from supplier tab. THIS WORKS
SupplierOrderDate = Cells(FromRow, 3) 'THIS WORKS
With Sheets("OpenOrders").Range("b1:c2000")
Set C = .Find(ID, LookIn:=xlValues, LookAt:=xlWhole) 'THIS WORKS
If Not C Is Nothing Then 'THIS WORKS, C DOES CONTAIN THE ID#
FoundRow = C.Row ' get row where ID is found
x = FoundRow & ":" &
Rows(x).Select

With Sheets("OpenOrders").Range(x)
' === the following does not work. SupplierOrderDate not found.
' Variable shows "08/31/2009".
' ==== order date it should be matching to is "08/31/09".
' Both date columns are formatted the same
Set D = .Find(SupplierOrderDate, LookIn:=xlValues)
If Not D Is Nothing Then
OrdersOrderDate = Cells(FoundRow, 3)
End If
End With

End With

FOUND ANSWER--PLEASE IGNORE.
 

FOUND THE ANSWERS. PLEASE IGNORE.

Thanks.




"laavista" wrote:

I have two worksheets. One called "OpenOrders" and one called
"Supplier". I need to determine if the supplier id in the Supplier
worksheet matches the id in the OpenOrders AND if the order dates in both
match. It seems so simple, but nothing I'm trying is working.

The following DOES find the ID, but I can't pick up the order date in the
ID's row so I can compare it with the order date in supplier. After I found
the ID, I tried picking up the date using:
OrdersOrderDate = Cells(FoundRow, 3) ', e.g., foundrow=2
This didn't work. I then tried the following (which also doesn't work)

Your help would be GREATLY appreciated!

Dim FoundRow As String
Dim C As Range
Dim D As Range

With Sheets("Supplier").Range("b1:f2000")
ID = Cells(FromRow, 2) ' get ID from supplier tab. THIS WORKS
SupplierOrderDate = Cells(FromRow, 3) 'THIS WORKS
With Sheets("OpenOrders").Range("b1:c2000")
Set C = .Find(ID, LookIn:=xlValues, LookAt:=xlWhole) 'THIS WORKS
If Not C Is Nothing Then 'THIS WORKS, C DOES CONTAIN THE ID#
FoundRow = C.Row ' get row where ID is found
x = FoundRow & ":" &
Rows(x).Select

With Sheets("OpenOrders").Range(x)
' === the following does not work. SupplierOrderDate not found.
' Variable shows "08/31/2009".
' ==== order date it should be matching to is "08/31/09".
' Both date columns are formatted the same
Set D = .Find(SupplierOrderDate, LookIn:=xlValues)
If Not D Is Nothing Then
OrdersOrderDate = Cells(FoundRow, 3)
End If
End With

End With

find TWO values in a worksheet
 

Finding dates is a bit tricky... check out this link at the bottom of the
page...

http://www.cpearson.com/excel/DateTimeVBA.htm
--
HTH...

Jim Thomlinson


"laavista" wrote:

I have two worksheets. One called "OpenOrders" and one called
"Supplier". I need to determine if the supplier id in the Supplier
worksheet matches the id in the OpenOrders AND if the order dates in both
match. It seems so simple, but nothing I'm trying is working.

The following DOES find the ID, but I can't pick up the order date in the
ID's row so I can compare it with the order date in supplier. After I found
the ID, I tried picking up the date using:
OrdersOrderDate = Cells(FoundRow, 3) ', e.g., foundrow=2
This didn't work. I then tried the following (which also doesn't work)

Your help would be GREATLY appreciated!

Dim FoundRow As String
Dim C As Range
Dim D As Range

With Sheets("Supplier").Range("b1:f2000")
ID = Cells(FromRow, 2) ' get ID from supplier tab. THIS WORKS
SupplierOrderDate = Cells(FromRow, 3) 'THIS WORKS
With Sheets("OpenOrders").Range("b1:c2000")
Set C = .Find(ID, LookIn:=xlValues, LookAt:=xlWhole) 'THIS WORKS
If Not C Is Nothing Then 'THIS WORKS, C DOES CONTAIN THE ID#
FoundRow = C.Row ' get row where ID is found
x = FoundRow & ":" &
Rows(x).Select

With Sheets("OpenOrders").Range(x)
' === the following does not work. SupplierOrderDate not found.
' Variable shows "08/31/2009".
' ==== order date it should be matching to is "08/31/09".
' Both date columns are formatted the same
Set D = .Find(SupplierOrderDate, LookIn:=xlValues)
If Not D Is Nothing Then
OrdersOrderDate = Cells(FoundRow, 3)
End If
End With

End With

find TWO values in a worksheet
 

This was really helpful! Thanks so much.



"Jim Thomlinson" wrote:

Finding dates is a bit tricky... check out this link at the bottom of the
page...

http://www.cpearson.com/excel/DateTimeVBA.htm
--
HTH...

Jim Thomlinson


"laavista" wrote:

I have two worksheets. One called "OpenOrders" and one called
"Supplier". I need to determine if the supplier id in the Supplier
worksheet matches the id in the OpenOrders AND if the order dates in both
match. It seems so simple, but nothing I'm trying is working.

The following DOES find the ID, but I can't pick up the order date in the
ID's row so I can compare it with the order date in supplier. After I found
the ID, I tried picking up the date using:
OrdersOrderDate = Cells(FoundRow, 3) ', e.g., foundrow=2
This didn't work. I then tried the following (which also doesn't work)

Your help would be GREATLY appreciated!

Dim FoundRow As String
Dim C As Range
Dim D As Range

With Sheets("Supplier").Range("b1:f2000")
ID = Cells(FromRow, 2) ' get ID from supplier tab. THIS WORKS
SupplierOrderDate = Cells(FromRow, 3) 'THIS WORKS
With Sheets("OpenOrders").Range("b1:c2000")
Set C = .Find(ID, LookIn:=xlValues, LookAt:=xlWhole) 'THIS WORKS
If Not C Is Nothing Then 'THIS WORKS, C DOES CONTAIN THE ID#
FoundRow = C.Row ' get row where ID is found
x = FoundRow & ":" &
Rows(x).Select

With Sheets("OpenOrders").Range(x)
' === the following does not work. SupplierOrderDate not found.
' Variable shows "08/31/2009".
' ==== order date it should be matching to is "08/31/09".
' Both date columns are formatted the same
Set D = .Find(SupplierOrderDate, LookIn:=xlValues)
If Not D Is Nothing Then
OrdersOrderDate = Cells(FoundRow, 3)
End If
End With

End With