• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup Multiple value

tpracsg

New Member
Hi,
I need a Vlookup help for small billing menu. For this I am attaching herewith a sample excel file.
My requirements: when I select order number in billing sheet, display items, quantity, rate, value details from order as per attachment.
Request to help me.
Thanks in advance.
CSGanesh
 

Attachments

tpracsg

New Member
hi @tpracsg ,

PFA VBA Solution.
Dear Sir
Thank you for your reply. But it will shows 'run time error : 1004'
I furnish below debug error

>>> use code - tags <<<
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim lrow As Long

lrow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("D1")) Is Nothing Then

    Range("A5:F100000").Clear
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False

    Sheets("Sheet1").Range("A1:F" & lrow).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Y1:AD2"), CopyToRange:=Range("A5"), Unique:=False

    Columns("A:A").EntireColumn.AutoFit
    Columns("A:B").Select
    Selection.Clear
    Range("D1").Select
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Please advise.
 
Last edited by a moderator:

rahulshewale1

Active Member
Dear Sir
Thank you for your reply. But it will shows 'run time error : 1004'
I furnish below debug error

>>> use code - tags <<<
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim lrow As Long

lrow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("D1")) Is Nothing Then

    Range("A5:F100000").Clear
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False

    Sheets("Sheet1").Range("A1:F" & lrow).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Y1:AD2"), CopyToRange:=Range("A5"), Unique:=False

    Columns("A:A").EntireColumn.AutoFit
    Columns("A:B").Select
    Selection.Clear
    Range("D1").Select
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Please advise.

try this

>>> use code - tags <<<
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim LROW As Long

LROW = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row

If Not Intersect(Target, Range("D1")) Is Nothing Then

    Range("A5:F100000").Clear
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("A1:F" & LROW).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Y1:AD2"), CopyToRange:=Range("A5"), Unique:=False
    Columns("A:A").EntireColumn.AutoFit
    Columns("A:B").Select
    Selection.Clear
    Range("D1").Select
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Attachments

Last edited by a moderator:
Top