View Full Version : Lookup Help

11-17-2008, 02:20 PM
In my last thread I did not explain exactly what I was needing. In Excel I need to lookup some info from one spread sheet and place it into another. A traditional Lookup would be fine here, but doing so would only return one piece of data, I will need to return more than one piece of data. For example, I am trying to return some data that was logged into excel via the date it was run. Below is a sample of what data I am looking at before hand.

Part # Parts Ran
1 11/16/08 234-EA 1800
2 11/17/08 456-EA 1000
3 11/17/08 567-EA 1500
4 11/17/08 234-EA 2000
5 11/18/08 123-EA 1000

I want to see everything that was ran on 11/17/08 on a different spread sheet, but do not want to see what was ran any other day. Is this possible?

11-17-2008, 02:46 PM
what's the problem with using auto filter and then copying to new spreadsheet?

11-17-2008, 07:59 PM
Because I am trying to automate a report to view at the start of the day.

11-17-2008, 09:48 PM
yuo can use something like that:

Sub Macro1()
Dim FirstRow, LastRow, Diff As Long
Dim Yesterday As Date
Yesterday = Date - 1
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
FirstRow = Cells.Find(What:=Yesterday, After:=[B1], LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True).Row

LastRow = Cells.Find(What:=Yesterday, After:=[B1], LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=True).Row

Diff = LastRow - FirstRow + 2
Sheets("Sheet2").Range("A2:D" & Diff).Value = _
Sheets("Sheet1").Range("A" & FirstRow & ":D" & LastRow).Value
End Sub