Lookup Function
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Lookup Function

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Washington State
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Probably an easy one. I need to modify the following function to allow Excel to search multiple worksheets for a value. Example...below lookup will look in the sheet "Finished Goods Inventory" for a value. This function works perfectly.

    I would like to have it look in sheets "Finished Goods Inventory" and "Raw Inventory" both for the same value. Both the sheets mentioned are in the same workbook. I think this is possible, but I am not sure. Thanks in adavance for any help.

    Rick

    Function DoLookup(ID As String, strDesc As String, curPrice As Currency, lQty As Long, nRow As Long)
    'After barcode is scanned do the lookup
    Dim nLastRow As Long

    'get last row containing data
    nLastRow = Sheets("Finished Goods Inventory").Cells(65000, 1).End(xlUp).Row

    With Worksheets(1)
    Set c = .Range("a2:a" & Format$(nLastRow)).Find(ID, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    If c Is Nothing Then
    DoLookup = False
    nRow = 0
    Exit Function
    Else
    nRow = c.Row
    Rows(nRow).Select
    strDesc = c.Offset(0, 1).Value
    curPrice = c.Offset(0, 2).Value
    lQty = c.Offset(0, 3).Value
    DoLookup = True
    End If
    End With

    End Function

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try (Untested)


    Function DoLookup(ID As String, strDesc As String, curPrice As Currency, lQty As Long, nRow As Long)
    'After barcode is scanned do the lookup
    Dim nLastRow As Long
    Dim c
    Dim Sh As Worksheet '<<

    For Each Sh In ThisWorkbook.Sheets '<<
    If Sh.Name = "Finished Goods Inventory" Or Sh.Name = "Finished Goods Inventory" Then

    'get last row containing data
    nLastRow = Sheets("Finished Goods Inventory").Cells(65000, 1).End(xlUp).Row

    With Sh
    Set c = .Range("a2:a" & Format$(nLastRow)).Find(ID, LookIn:=xlValues, _
    lookat:=xlWhole, MatchCase:=True)
    If c Is Nothing Then
    DoLookup = False
    nRow = 0
    Exit Function
    Else
    nRow = c.Row
    Rows(nRow).Select
    strDesc = c.Offset(0, 1).Value
    curPrice = c.Offset(0, 2).Value
    lQty = c.Offset(0, 3).Value
    DoLookup = True
    End If
    End With
    End If
    Next
    End Function




    Kind Regards,
    Ivan F Moala From the City of Sails

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com