CODEWITHSUNDEEP
excelvba
David Cullmer
David Cullmer

Reputation: 1

Adding a order to master inventory using excel macros

I have two separate excel workbooks. One is an order form, the other is a master inventory file.

I have a column where I input the order amount for each individual items (let's say it spans from cell C2:C130, each row is a different item). My optimal solution is to develop a macro that takes that order and adds it to an existing master inventory to keep track of total orders.

I wrote some code that I thought would work:

Private Sub CommandButton1_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Variant
Dim lDestLastRow As Variant
Workbooks.Open "C:\Users\Dave\Desktop\Designs Work\Master_Inventory.xlsm"
Set wsCopy = Workbooks("Production file_Dave Edits").Worksheets("Order")
Set wsDest = Workbooks("Master_Inventory").Worksheets("Sheet1")
Set lCopyLastRow = wsCopy.Range("E2:E130")
Set lDestLastRow = wsDest.Range("E2:E130")
lDestLastRow.Value = lDestLastRow.Value + lCopyLastRow.Value
End Sub

when I run this code, I get a mismatch error (type 13?).

So I went into the watch window to see the type of each expression and the combined right side of the equation is a "variant/integer" type, whereas each individual expression is a "variant/variant" type. Moreover, when I run the code and call only one cell instead of a matrix, the code works; it doesn't run when multiple cells are called.

Can anyone help? Or have a more elegant code? Thank you

Upvotes: 0

Views: 131

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

Set lCopyLastRow = wsCopy.Range("E2:E130")
Set lDestLastRow = wsDest.Range("E2:E130")

This makes both variables Variant/Range, because the Set keyword says the right-hand side of the assignment operator is yielding an object reference: the two variables might as well be declared As Range.

Now, the Value of a Range object that refers to multiple cells, is a 2D Variant array.

lDestLastRow.Value = lDestLastRow.Value + lCopyLastRow.Value

That's where you're getting the type mismatch error, because you can't use the + operator with array operands.

when I run the code and call only one cell instead of a matrix, the code works

That's because a single-cell Range.Value returns that numeric value, and + will work with that - although, if the cell contains an error value (e.g. #REF! or #VALUE!), you'll still get a type mismatch error, because a Variant/Error can't be an operand.

I wish I could help beyond that, but I have no idea what this + intends to be doing.

As for a more elegant solution, I'd recommend indenting the procedure body, moving declarations closer to their assignment, and keeping a reference to the destination "inventory" workbook:

Private Sub CommandButton1_Click()

    Dim sourceSheet As Worksheet
    Set sourceSheet = Workbooks("Production file_Dave Edits").Worksheets("Order")

    Dim inventoryBook As Workbook
    Set inventoryBook = Workbooks.Open("C:\Users\Dave\Desktop\Designs Work\Master_Inventory.xlsm")

    Dim destSheet As Worksheet
    Set destSheet = inventoryBook.Worksheets("Sheet1")

    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("E2:E130")

    Dim destRange As Range    
    Set destRange = destSheet.Range("E2:E130")

    'todo: figure out intent
    'lDestLastRow.Value = lDestLastRow.Value + lCopyLastRow.Value

End Sub

If that + intends to add everything up in both ranges, you could use Application.WorksheetFunction.Sum(sourceRange) + Application.WorksheetFunction.Sum(destRange), although I doubt that's really want you're looking for.

Upvotes: 2

Related Questions