| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find values from one worksheet in other worksheets | Excel Programming | |||
| How to Find values in one worksheet and copy them to a new workshe | Excel Programming | |||
| Find two values in worksheet to return one value | Excel Worksheet Functions | |||
| Find corresponding values on another worksheet | Excel Discussion (Misc queries) | |||
| How do I find and replace "values" (like #N/A) in a worksheet? | Excel Discussion (Misc queries) | |||
Linear Mode
