excel - How to establish third variable for Dimmed variable

Question

This is my first post here on this platform and I hope I will be able to get some help. I am very new to excel VBA, so please forgive me if I do not use the appropriate jargon or language!

I am working on an inventory management system that uses excel VBA. It relies on a Userform which retrieves the current inventory for each SKU. The person updating chooses from 4 types of quantity updates which comprises of:

a) Picking list (inventory to be withdrawn for projects), b) Incoming stock (receiving), c) Return stock (leftover/balance SKUs not utilised from projects) d) Item transfer (transfer of SKUs from one warehouse to another)

The process is as follows:

1. Staff to launch inventory update.
2. Fill in relevant details pertaining to the update type (eg. Picking list will be linked to a project task no. etc.)
3. Update necessary update type with the relevant SKUs, warehouse, and quantities
4. Confirm the quantities before submitting the figures to the respective excel sheets
5. Once submitted, the information will interface over to the respective excel sheet where staff will be able to a) save as PDF for filing purposes, and b) execute a command which subtracts/adds the quantities on the inventory master sheet based on the respective SKUs. This has also been coded by VBA however, I am having issues when it comes to updating quantities when it comes to SKUs for both warehouses.

ISSUES I AM FACING:

I am using Dim as range to establish my variables (eg. rng1, rng2, rng3, cell1, cell2, cell3). There is no problem with executing the macro with 4 variables (rng1, rng2, cell1, cell2), but when introducing rng3 and cell3, the macro has issues looping.

    Sub VosToNetTransfer()

MsgBox "Transferred product(s) to NETLINE successfully!"

Dim rng1, rng2, rng3, cell1, cell2, cell3 As Range

Dim lastRow1 As Long
lastRow1 = Sheets("VosToNet").Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Worksheets("VosToNet").Range("B13:B" & lastRow1)

Dim lastrow2 As Long
lastrow2 = Sheets("InventoryMaster").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Worksheets("InventoryMaster").Range("A4:A61" & lastrow2)

Dim lastrow3 As Long
lastrow3 = Sheets("InventoryMaster").Range("A" & Rows.Count).End(xlUp).Row
Set rng3 = Worksheets("InventoryMaster").Range("A65:A87" & lastrow3)



For Each cell1 In rng1
    If IsEmpty(cell1.Value) Then Exit For

    For Each cell2 In rng2
        If IsEmpty(cell2.Value) Then Exit For
        
        For Each cell3 In rng3
        If IsEmpty(cell3.Value) Then Exit For
        
    If cell1 <> cell2 Then Exit For
    If cell2 <> cell3 Then Exit For
    If cell3 <> cell1 Then Exit For
         
            If cell2 = cell1 Then
                cell2.Offset(0, 2) = cell2.Offset(0, 2) + cell1.Offset(0, 2)
                cell3.Offset(0, 2) = cell3.Offset(0, 2) - cell1.Offset(0, 2)
                             
             End If
                
         Next cell3
         Next cell2
         Next cell1          

End Sub

This is the current code that I am using for trying to transfer quantity from one warehouse to another. However, it does not work.

Basically, my rng1 is the SKU code on the update sheet, rng2 is the SKU code on the Master sheet. When cell1 matches cell2, that particular quantity is subtracted/added by .Offset. However, my limitations are such that I am working with SKUs which some of which are available in both warehouses, therefore when it comes to updating quantities for both warehouses assumingly I think I would have to establish another rng3 for the SKU code for the other warehouse. How am I able to update quantities for different warehouses with a single command button? Additionally, how am I able to transfer quantity from one warehouse to another warehouse based on the update sheet?

I will be able to provide as many supporting details/images as possible if you would like more clarification.

TLDR; I want to be able to establish a third set of variables (rng3, cell3) such that; when cell1 = cell2, then subtract/add accordingly from SKU in rng2. when cell2 = cell3, then subtract/add accordingly from SKU in rng3.

Thank you in advance!

question from:https://stackoverflow.com/questions/65951935/how-to-establish-third-variable-for-dimmed-variable

Answer 1

Comparing Cells of Ranges

• Note that this is an 'introductory' (learning) code. It can be improved on multiple accounts (using Application.Match, using arrays, using one loop only (in another procedure), better cell value testing...).
• It will loop through all cells (outer loop) of each of the Destination Ranges and compare their values to the values of the cells (inner loop) of the Source Range and do the required operation if matched. Exit For will exit the inner loop when a match is found.

The Code

Option Explicit

Sub VosToNetTransfer()
    
    's - Source: read from
    'd - Destination: write to
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' Workbook containing this code.
    
    MsgBox "Transferred product(s) to NETLINE successfully!" '?
    
    Dim srg As Range, drg As Range
    Dim sCell As Range, dCell As Range
    Dim LastRow As Long
    
    ' Define Source Range.
    LastRow = wb.Worksheets("VosToNet").Range("A" & Rows.Count).End(xlUp).Row
    Set srg = wb.Worksheets("VosToNet").Range("B13:B" & LastRow)
    
    ' Define First Destination Range.
    Set drg = wb.Worksheets("InventoryMaster").Range("A4:A61")
    
    For Each dCell In drg.Cells
        If Len(dCell.Value) > 0 Then
            For Each sCell In srg.Cells
                If sCell.Value = dCell.Value Then
                    dCell.Offset(, 2).Value = dCell.Offset(, 2).Value _
                        + sCell.Offset(, 2).Value
                    Exit For
                End If
            Next sCell
        End If
    Next dCell
        
    ' Define Second Destination Range.
    Set drg = wb.Worksheets("InventoryMaster").Range("A65:A87")
    
    For Each dCell In drg.Cells
        If Len(dCell.Value) > 0 Then
             For Each sCell In srg.Cells
                 If sCell.Value = dCell.Value Then
                     dCell.Offset(, 2).Value = dCell.Offset(, 2).Value _
                         - sCell.Offset(, 2).Value
                     Exit For
                 End If
             Next sCell
        End If
    Next dCell

End Sub