A Way to edit an inventory list through a macro in a different workbook?

[whahmira]

Hello! Right now I have an inventory list that has a macro in it where you can manually add parts onto a reserve so that other people will not use them.
Basically you just type in the quantity and the part number and it will look up the part number within the inventory and add whatever quantity was specified to the 'On Reserve' cell in that row.

What I am trying to do is change that process so it happens automatically. I have a work order template with a button "Book". When that button is clicked, I would like the macro to find the list of part numbers and their quantities on the work order and then open up the inventory list and find each of the part numbers and then change their On Reserve amount.

This is what I have:

[LEFT][COLOR=#333333][FONT=Verdana][COLOR=#333333][FONT=monospace]Sub Inventory()
Sheets("Overview").Activate
Dim PartNo(100) As String
Dim Quantity(100) As Integer
Dim iRow As Integer
   iRow = 27 'start one row early or it will skip the first row of the order
   Do Until IsEmpty(Cells(iRow, 1))
   iRow = iRow + 1
   PartNo(iRow) = Cells(iRow, 1).Value
   Quantity(iRow) = Cells(iRow, 4).Value  
   Loop
maxRow = iRow
iRow = 28
Workbooks.Open Filename:="link to inventory"

If Quantity(iRow) = 0 Then
MsgBox "Please check quantities"
Else: Sheets("Database").Select
Set zelle = Cells.Find(What:=PartNo(iRow), After:= _
 ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 'MsgBox zelle.Address
 zelle.Select
Sales2018 = Cells(zelle.Row, 20).Value
OnReserve = Cells(zelle.Row, 5).Value
Cells(zelle.Row, 5).Value = OnReserve + Quantity
Cells(zelle.Row, 20).Value = Sales2018 + Quantity
MsgBox PartNo & "was booked successfully"
End If
   
ActiveWorkbook.Save
ActiveWindow.Close
    
End Sub

[/FONT][/COLOR][/FONT][/COLOR][/LEFT]

When I run that it says "type mismatch" referring to the addition symbol in:

[LEFT][COLOR=#333333][FONT=monospace]Cells(zelle.Row, 5).Value = OnReserve + Quantity[/FONT][/COLOR][/LEFT]

​

This post is cross-referenced with the post below, which contains the workbooks as well for reference.
https://www.excelforum.com/excel-pr...-through-a-macro-in-a-different-workbook.html

Any help would be greatly appreciated
​

 

[whahmira]

Now the code is at this point:

[LEFT][COLOR=#333333][FONT=monospace]Sub Inventory()

Sheets("Order Form").Activate
Dim PartNo(100) As String
Dim Quantity(100) As Integer

Dim iRow As Integer
   iRow = 8 
   Do Until IsEmpty(Cells(iRow, 1))
   iRow = iRow + 1
   PartNo(iRow) = Cells(iRow, 1).Value
   Quantity(iRow) = Cells(iRow, 4).Value
   Loop
maxRow = iRow
iRow = 9

Workbooks.Open Filename:="C:\Users\Z645352\Desktop\Test1\WorkbookB.xlsm"

Sheets("Database").Select
Set zelle = Cells.Find(What:=PartNo(iRow), After:= _
 ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
 SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 zelle.Select
Sales2018 = Cells(zelle.Row, 5).Value
OnReserve = Cells(zelle.Row, 4).Value

Dim nRow As Integer
    nRow = 3
    Do Until iRow = maxRow
If Quantity(iRow) > 0 Then
Cells(nRow, 4).Value = OnReserve + Quantity(iRow)
Cells(nRow, 5).Value = Sales2018 + Quantity(iRow)


     kRow = kRow + 1
End If
     iRow = iRow + 1

    Loop


ActiveWorkbook.Save
ActiveWindow.Close
    
End Sub[/FONT][/COLOR][/LEFT]

Like I said, the workbooks can be seen in the original post in the link to excel forum
the code will still not run!

 

[whahmira]

This thread was solved in its original post in excel forum. The answer can be seen there