Macro or Lookup?

[Utradeshow]

Hi Guys, I have a workbook on SHEET 1 I have a running database actually a tracking system. It consiststs of COLUMNS "A" through "J"

Colums G through J have dates in them. I am looking to see if I can have sheet 2 have an input cell where I enter the date and it will list the rows containing the dates? Here is an example of the first sheet.

Trio Tracking 2006 Test.xls
	A	B	C	D	E	F	G	H	I	J
2	12345	B.E.	Customer	Client			01/03/06	01/04/06	01/07/06	01/07/06
3	901508	B.E.	Customer	Client			01/03/06	01/04/06	01/07/06	01/07/06
4	901509	B.E.	Customer	Client			01/03/06	01/04/06	01/07/06	01/07/06
TRADESHOW

 

[erik.van.geit]

1. I just can't seem to make it run automatically.
2. Also if it could copy out to column "M" I think it would be perfect.

1. what do you mean, when the dates are changed ?
2. do you mean you want to copy columns A to M ?
then just change "J" to "M" in the line with COPY

greetings,
Erik

 

[Utradeshow]

Ok, Almost perfect... I changed it to copy to "M" works great.
I'm finding out I only need the code to Look at The dates in "H" and "J" can we change that?
And I still have to make it run Manually. I placed the code in the"Weekend" sheet?

 

[erik.van.geit]

only H and J
edit the line with "Range("G" & i & ":J" & i)"
with a comma instead of the colon
test this

Range("H2:J2").Select
Range("H2,J2").Select

you need

Range("H" & i & ",J" & i)

And I still have to make it run Manually. I placed the code in the"Weekend" sheet?

perhaps my question wasn't well worded
when do you want to run the code: is it when you change one of the dates ?
Then there is a problem to solve. When you want to change the dates, the code may not run when you didn't change both. Would it be good to force the user to clear before he can fill in a next date.-?

the code can then be place in th worksheetchange-event, with a few tweaks ...

 

[Utradeshow]

Eric,

Ok, Here is the final product that works awesome!!! The only thing is I have the input cells in row 1, then the code places the result in rows 2 DOWN. is there a way to have it start at about row 5?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$H$1" Then Exit Sub
If Len(Target.Value) = 0 Then Exit Sub
Dim TradeSh As Worksheet
Dim WeekendSh As Worksheet
Dim TradeLR As Long
Dim WeekendLR As Long
Dim i As Long
Dim c As Range
Dim StartDate As Date
Dim EndDate As Date
Dim FindDate As Date
Dim AppSetCalc As Integer

Set TradeSh = Sheets("TRADESHOW")
Set WeekendSh = Sheets("WEEKEND")

    With WeekendSh
    .Range("A2:M" & Rows.Count).Delete
    StartDate = .Range("G1").Value
    StartDate = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
    EndDate = .Range("H1").Value
    EndDate = DateSerial(Year(EndDate), Month(EndDate), Day(EndDate))
    
    End With
    
    With Application
    .ScreenUpdating = False
    AppSetCalc = .Calculation
    .Calculation = xlCalculationManual
    End With

    With TradeSh
    TradeLR = .Cells.Find("*", .[A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
        For i = 2 To TradeLR
        Set c = Nothing
            For FindDate = StartDate To EndDate
            Set c = .Range("H" & i & ",J" & i).Find(FindDate)
                If Not c Is Nothing Then
                .Range(.Cells(i, "A"), .Cells(i, "M")).Copy WeekendSh.Cells(Rows.Count, "A").End(xlUp)(2)
                Exit For
                End If
            Next FindDate
        Next i

    End With
    
    With Application
    .ScreenUpdating = True
    .Calculation = AppSetCalc
    Columns("G:J").NumberFormat = "m/d;@"
    
    End With

End Sub

 

[erik.van.geit]

to make it start at row 5 you could insert some formulas & extra lines (my prefered strategy )
or you could use a simple trick
put something in row 4 before the action takes place
after action delete it: so simple

    With TradeSh 
    .Range("A4") = "something "

    TradeLR = ....
    ....

    .Range("A4") = ""
    End With

greetins from Belgiums,
Erik
possibly without internet-connection within 2 hours till 22th sept

 

[Utradeshow]

Thank you, Works GREAT!! Save me MUCHO Time