Complicated Lookup by Date

**abertrand** · 02-04-2009, 06:09 PM

I'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.

The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.

For clarification Work Center would be the abbreviation (letter code) about the dates.

P.S. I think my brain is turning to mush staring at this, so any help is greatly appreciated.

**stanleydgromjr** · 02-04-2009, 10:25 PM

abertrand,

I'm trying to create a macro that allows a date to be entered into a Dialog box that is activated by a short cut key.

What exactly would you enter in the "Dialog" box? Examples please.

The macro would use this date to look through all the information on tab Output, and if(as) it finds dates that match the information will then be filled out on tab Schedule.

With examples of a "date", please manually complete sheet "Schedule".

For clarification Work Center would be the abbreviation (letter code) about the dates.

Again, examples please.

Please attach another workbook with examples on sheet "Schedule".

Have a great day,
Stan

**shg** · 02-05-2009, 12:42 AM

abertrand,

Please read the forum rules about thread titles before posting again.

**abertrand** · 02-05-2009, 09:27 AM

Here is the same file with an example of what i'm trying to get a macro to do for me. The information in the 'Schedule' tab if for the date 1/11/09. I only went through the first 100 lines, but need a macro that will go through all of it.

I also, would like to have the macro prompt to enter this date into a box with the use of a shortcut key.

**stanleydgromjr** · 02-06-2009, 09:24 AM

abertrand,

Thanks for the new workbook.

On the "Schedule" sheet, are you looking for formulae pointing to the "Output" sheet, or the values?

=Output!P1

or

PI

Have a great day,
Stan

**abertrand** · 02-06-2009, 11:00 AM

I'm not sure I completely understand the question, but will do my best to answer.

I believe the answer is the Output sheet, as this is were all the calculated information is shown, and the information needs to correspond to certain items on this sheet.

I'm not certain this clarifies much, but hope it helps.

**stanleydgromjr** · 02-06-2009, 05:23 PM

abertrand,

Here you go - nice challenge.

See the attached workbook "InputBox for date to search sheet Output get offset data information and paste to sheet Schedule - Hot List LOB's(1) - abertrand - SDG.xlsm".

To run the "CompleteSchedule" macro, you can use the:
Keyboard Shortcut: Ctrl+Shift+S

Have a great day,
Stan

**abertrand** · 03-20-2009, 01:12 PM

Any ideas why this code would work before and after inserting another column at the beginning, and more data, it will only return one line of results?

I am having problems posting the file but will keep trying.

Thanks,

Code:

   Dim LR, LR2, Ctr, Ctr2, MyLR, LC As Long
    Dim rng As Range
    Dim SearchDate As Date
    Sheets("Output").Select
    On Error Resume Next
    SearchDate = InputBox("Enter the search date, 'm/d' (month/day), example:  1/1   12/31  ")
    If SearchDate = 0 Then
        Application.ScreenUpdating = True
        MsgBox "You clicked on 'Cancel', or you did not enter a date - macro terminated."
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LR3 = Sheets("Schedule").Cells(Rows.Count, 1).End(xlUp).Row + 1
    With Sheets("Output")
        LR = .Cells(Rows.Count, 3).End(xlUp).Row
        LC = .Cells(1, Columns.Count).End(xlToLeft).Column - 1
        Set rng = .Range("C1:C" & LR)
        MyLR = Application.WorksheetFunction.Match(0, rng, 0) - 1
        For Ctr = 2 To MyLR Step 3
            For Ctr2 = 4 To LC Step 1
                On Error Resume Next
                If .Cells(Ctr, Ctr2) = "#N/A" Then
                    'Do nothing
                ElseIf .Cells(Ctr, Ctr2) = SearchDate Then
                    Sheets("Schedule").Cells(LR3, 1) = .Cells(Ctr - 1, Ctr2).Value
                    Sheets("Schedule").Cells(LR3, 2) = .Cells(Ctr + 1, 3).Value
                    Sheets("Schedule").Cells(LR3, 3) = .Cells(Ctr - 1, 4).Value
                    Sheets("Schedule").Cells(LR3, 4) = .Cells(Ctr + 1, 3).Value
                    Sheets("Schedule").Cells(LR3, 5) = .Cells(Ctr, 4).Value
                    Sheets("Schedule").Cells(1, 5) = SearchDate
                    LR3 = LR3 + 1
                End If
            Next Ctr2
        Next Ctr
    End With
    Sheets("Schedule").Select
End Sub