Excel coding using a query or recordset

[scroce]

I know I'm going to have some trouble with this, so I figured I'd post it now. I'm not even sure I can do this in excel -
but here is some pseudo-pseudo code of what i want to happen. Everything could be contained within 1 workbook and he names of the objects have been changed to protect the innocent.


1. Open a recordset that includes ColumnB, ColumnI and ColumnJ from Sheet1.

2. Limit the recordset to reflect only those records which have the string "pr" somewhere in ColumnI.

3. Lookup whatever value currently exists in Sheet2 cell B4 in ColumnB of the recordset and return the corresponding value from ColumnJ in the recordset to cell G4 on Sheet2

4. If the value in Sheet2 cell B4 does not exist in ColumnB of the recordset, then return the string "Not Found" to cell G4 on Sheet2.

I hope this isn't too confusing. It doesn't seem like it should be too hard to do, but I'm only an occasional programmer in excel and forget how to manipulate the object model

Any help would be greatly appreciated.
How much more water would there be in the ocean if it weren't for sponges?

 

[Mossoft]

Try something like this:

In Sheet2 G4:

=IF(VLOOKUP(B3,Sheet1!B:J,9,FALSE)=0,"Not Found",IF(ISERROR(SEARCH("PR",G7,1)),"Not Found",VLOOKUP(B3,Sheet1!B:J,9,FALSE)))

M

 

[scroce]

looks simple thank you - i'll let you know how it goes - maybe it'll save a few hours of code research. How much more water would there be in the ocean if it weren't for sponges?