Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting 1 from inventory (VB/Excel 2000)

    Thanks....

    I haven't tried it yet but I am sure it will work fine

    Thom

  2. #2
    Lounger
    Join Date
    Jan 2005
    Location
    Colorado Springs, Colorado, USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtracting 1 from inventory (VB/Excel 2000)

    Edited by HansV to restore <!t>[E]<!/t> (it had been converted to <img src=/S/schwa.gif border=0 alt=schwa width=7 height=9> by the Lounge software)

    HI

    I have my inventory in Excel. My POS will export a list of SKU#s, that I have sold, to a spreadsheet (Send me an email if you wish to know why they are not one in the same). I wish to run a Macro that will compare the list of sold items (from POS) with the inventory (in Excel) and then subtract the number sold from the quantity column in Excel. Or... if there is a better way I am all ears.

    Sample;
    POS exports SKU# in Workbook[1].Column[A]
    Excel SKU# is in Workbook[2].Sheet[1].Column<!t>[E]<!/t> & Quantity is in Workbook[2].Sheet[1].Column[H]

    Thanks
    Thom

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting 1 from inventory (VB/Excel 2000)

    Edited by Legare Coleman 1/27/2005 to change column A to E after Hans fixed the original post.

    The code below, placed in the inventory workbook, should do what you want. There was a glitch in you message that made the column in the inventory workbook where the SKU is unclear. The code below assumes column A. If that is not correct, the code will need to be changed. When run, the code will ask you to find the POS file.

    <pre>Public Sub ProcessPOS()
    Dim oPOSWB As Workbook, oPOSWS As Worksheet, oInvWS
    Dim vPOSWB As Variant, I As Long, J As Long
    Set oInvWS = ActiveWorkbook.Worksheets("Sheet1")
    vPOSWB = Application.GetOpenFilename(FileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*", _
    Title:="Open POS file")
    If vPOSWB = False Then Exit Sub
    Set oPOSWB = Workbooks.Open(Filename:=vPOSWB)
    Set oPOSWS = oPOSWB.Worksheets("Sheet1")
    For I = 0 To oPOSWS.Range("A65536").End(xlUp).Row - 1
    For J = 0 To oInvWS.Range("E65536").End(xlUp).Row - 1
    If oPOSWS.Range("A1").Offset(I, 0).Value = oInvWS.Range("E1").Offset(J, 0).Value Then
    If oInvWS.Range("H1").Offset(J, 0).Value > 0 Then
    oInvWS.Range("H1").Offset(J, 0).Value = oInvWS.Range("H1").Offset(J, 0).Value - 1
    Else
    MsgBox "Inventory item " & oInvWS.Range("E1").Offset(J, 0).Value & _
    " sold but inventory is zero."
    End If
    Exit For
    End If
    Next J
    If J > oInvWS.Range("A65536").End(xlUp).Row - 1 Then
    MsgBox "Item " & oPOSWS.Range("A1").Offset(I, 0).Value & " not found in inventory."
    End If
    Next I
    oPOSWB.Close
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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