lookup value if it match a criteria.

[coolhit]

Hi,

Please can someone help with lookup.

As its very new thing for me to do lookup like this one.

I have attached file if you could assist me.

User enters Date in "Bookings DAY COMPILER" sheet cell F2 and press the buttton.

Macro looks up the date which user has entered and matches the date in "Booking IN CALENDAR" and bring over column B(time) and C(delivery ref) in "Bookings DAY COMPILER" in B5(Time) and C5(Delivery Ref).

Please can someone assist me.

Many Thanks in advance.

[StephenR]

Try this:

Private Sub CommandButton1_Click()
 
Dim rData As Range

Application.ScreenUpdating = False

With Sheet4
    ActiveSheet.UsedRange.Offset(4).Clear
    .AutoFilterMode = False
    .Range("A4").AutoFilter Field:=1, Criteria1:=Range("F2").Value
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1, 1).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2)
        End If
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True
        
End Sub

[coolhit]

Try this:

Private Sub CommandButton1_Click()
 
Dim rData As Range

Application.ScreenUpdating = False

With Sheet4
    ActiveSheet.UsedRange.Offset(4).Clear
    .AutoFilterMode = False
    .Range("A4").AutoFilter Field:=1, Criteria1:=Range("F2").Value
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1, 1).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2)
        End If
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True
        
End Sub

Thankyou for the above code, works great. Could i ask for small favour.
if i want to extact other columns what part of codeing do i change or add.
Currently is does column B,C&D and if i want to add column H,K,M&N.

Once again many thanks.

[coolhit]

Thankyou for the above code, works great. Could i ask for small favour.
if i want to extact other columns what part of codeing do i change or add.
Currently is does column B,C&D and if i want to add column H,K,M&N.

Once again many thanks.

PLease could someone assist me with above.

[Andy Pope]

Use the OFFSET approach again,

not tested so resize or offset indexes may be out

        On Error GoTo 0
        If Not rData Is Nothing Then
' H
            set rData = rData.offset(,4).resize(,1)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,4)
' K
            set rData = rData.offset(,3)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,5)

' M:N
            set rData = rData.offset(,2).resize(,2)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,6)

        End If

[coolhit]

Use the OFFSET approach again,

not tested so resize or offset indexes may be out

        On Error GoTo 0
        If Not rData Is Nothing Then
' H
            set rData = rData.offset(,4).resize(,1)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,4)
' K
            set rData = rData.offset(,3)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,5)

' M:N
            set rData = rData.offset(,2).resize(,2)
            rData.Copy Range("B" & Rows.Count).End(xlUp)(2).offset(,6)

        End If

I tried this didn't work. Does column B,C&D from row 5 but H,K,M:N goes down from the last entry of BCD creating step effect going downwards.

Any ideas as i am new to resize and offset