Consulting

Results 1 to 6 of 6

Thread: Retrieve price from another wb instead of different sheet? code complete except exter

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    14
    Location

    Retrieve price from another wb instead of different sheet? code complete except exter

    I would like to open an invoice and use the macro to match an item# in column A to the wbPrice wsItems column A

    The following code does all this among the same workbook but I would rather have it look externally.

    "wsInvoice" instead will just be the active sheet and would search wbPrice-wsItems with the path C:\FileServer\CompanyDocs\My Excel\Price.xls

    please help


    Private Sub CommandButton1_Click()
        Dim wsItems As Worksheet, wsInvoice As Worksheet   '??????
        Dim iInvoice As Long, iLastInvoice As Long
        Dim iPrice As Long
        
        Application.ScreenUpdating = False
        
        Set wsItems = Worksheets("Items")          '  path would be:  C:\FileServer\CompanyDocs\My Excel\Price.xls
        Set wsInvoice = Worksheets("Invoice")      '  this would be obsolete  ... would just be referencing active sheet
        
        iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
        
        For iInvoice = 1 To iLastInvoice
            iPrice = 0
            
            On Error Resume Next    '   in case not found
            iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
            On Error GoTo 0
        
         If iPrice > 0 Then
                wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
         Else
                wsInvoice.Cells(iInvoice, 8).Value = ""
         End If
            
        Next iInvoice
        
        Application.ScreenUpdating = True
        
    End Sub
    Last edited by lostinvb; 03-14-2019 at 03:01 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested so you'll have to play around with this a bit


    Option Explicit
    Private Sub CommandButton1_Click()
        Dim wbPrice As Workbook
        Dim wsPrice As Worksheet
        
        Dim wsItems As Worksheet, wsInvoice As Worksheet   '??????
        Dim iInvoice As Long, iLastInvoice As Long
        Dim iPrice As Long
        
        Application.ScreenUpdating = False
        
        Set wsInvoice = ThisWorkbook.ActiveSheet
        
        Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Price.xls"
        Set wbPrice = ActiveWorkbook
        Set wsPrice = ActiveSheet
        
        ThisWorkbook.Activate
        
        iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
        
        For iInvoice = 1 To iLastInvoice
            iPrice = 0
            
            On Error Resume Next    '   in case not found
            iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsPrice.Columns(1), 0)
            On Error GoTo 0
        
            If iPrice > 0 Then
                wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
            Else
                wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
            End If
        Next iInvoice
        
        wbPrice.Close False
        
        Application.ScreenUpdating = True
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Mar 2019
    Posts
    14
    Location
    Paul, you are always my hero

    corrected the following and the test run worked fine. (will run a couple more tests)

    If iPrice > 0 Then
                wsInvoice.Cells(iInvoice, 8).Value = wsPrice.Cells(iPrice, 8).Value
            Else
                wsInvoice.Cells(iInvoice, 8).Value = "Not Found"
            End If

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by lostinvb View Post
    Paul, you are always my hero
    <blush>

    Good catch - hope the rest of the tests go OK

    BTW, I've always found that I do better (fewer bugs) when I have to have multiple workbooks open to always Set …. them and to refer to them explicitly

    I've seen macros trying to keep track of what the ActiveWorkbook is and it's just too much work -- let the computer keep track
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Mar 2019
    Posts
    14
    Location
    gotcha ....... I get lost with syntax when trying to specify but also in this case the invoices all have different names so naming them. But if it was a static invoice would the following be correct?

    Set wsInvoice = "C:\FileServer\CompanyDocs\My Excel\Invoice.xls"
    instead of:


    Set wsInvoice = ThisWorkbook.ActiveSheet
    (I hope this is what you were refering too)

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not really.

    In this ...


    Set wsInvoice = ThisWorkbook.ActiveSheet Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Price.xls" Set wbPrice = ActiveWorkbook Set wsPrice = ActiveSheet

    The WB containing the macro has the invoice WS selected, so I Set wsInvoice to that sheet

    Opening another WB makes that the ActiveWorkbook so I Set wbPrice to that WB so I don't get confused between which WB is active; I can just refer to wbPrice and be sure with no assumptions

    wbPrice has an ActiveWorksheet so I set wsPrice to THAT to be sure


    But if it was a static invoice would the following be correct?

    Set wsInvoice = "C:\FileServer\CompanyDocs\My Excel\Invoice.xls"
    Probably not. You can't Set something to a non-Object, and
    "C:\FileServer\CompanyDocs\My Excel\Invoice.xls" is a string

    You can do something like

    Set wsInvoice = ThisWorkbook.Worksheets ("Smith Invoice 2019-03-12")
    
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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