Vba macro

M

mamtasarkar

New Member
Joined
Oct 28, 2021
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
Hello,
Someone guide me, i create stock inventory management with two userforms for inward and outward and four sheets for opening stock, inward stock, outward and net stock with A:G rows A stands for items , B= MAKE, C= GRADE, D= OPSTOCK Quantity, E= Inward qty, F= outward and G= bal.quantity, i execute formula for import qty from op, inward and outward but i can't import items, make and grade rows without repeating from opening stock sheet and from inward sheet, please guide me how can i call three rows from two sheets
 
Sort by date Sort by votes
=IFERROR(1/ (1/SUMIFS(INWARD[Quantity],INWARD[Bearing],[@Bearing],INWARD[Make],[@Make])), " ")

=IFERROR(1/ (1/SUMIFS(Opstock[Quantity],Opstock[Bearing],[@Bearing],Opstock[Make],[@Make])), " ")
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
mamtasarkar said:
=IFERROR(1/ (1/SUMIFS(INWARD[Quantity],INWARD[Bearing],[@Bearing],INWARD[Make],[@Make])), " ")

=IFERROR(1/ (1/SUMIFS(Opstock[Quantity],Opstock[Bearing],[@Bearing],Opstock[Make],[@Make])), " ")
Click to expand...
This formula is in net stock sheet_ opening stock quantity and inward quantity columns
 
Upvote 0
OK, So you have your form to enter the bearings to the Inward sheet.

Then to transfer them to the stock inventory sheet (after they have been added to the inward sheet) run the following macro.

Read the comments to understand what the macro is doing.

VBA Code: 
Sub ProcessInward()
' Macro that transfers goods from Inwards sheet to Stock Inventory sheet

    Dim vInw, vStock
    Dim lRi As Long, lRs As Long, UBi As Long, UBs As Long
    Dim i As Integer
    Dim sBearing As String, sMake As String, sGrade As String
    Dim bFound As Boolean
    Dim rStock As Range
    
    ' load table in Inwards into array for fast processing
    vInw = Sheets("Inwards").Range("A1").CurrentRegion.Value
    UBi = UBound(vInw, 1) ' store number of rows
    
    ' loadtable in Stock Inventory into array for fast processing. Add sufficient extra rows to add any new items
    With Sheets("Stock Inventory").Range("A7")
        Set rStock = .Resize(.CurrentRegion.Rows.Count + UBi, .CurrentRegion.Columns.Count)
    End With
    vStock = rStock.Formula
    UBs = UBound(vStock, 1) ' store number of rows
    
    ' now go through each row (skip the header) in the inwards array _
      and check if same bearing, grade and make is already present in stock array
    For lRi = 2 To UBi
        'copy the bearing, make and grade into variables  to improve efficiency (removing any whitespace befor or after )
        sBearing = Trim(vInw(lRi, 1))
        sMake = Trim(vInw(lRi, 2))
        sGrade = Trim(vInw(lRi, 3))
        bFound = False
        ' Now loop through each row of vStock to compare (skip header)
        For lRs = 2 To UBs
            If sBearing Like vStock(lRs, 1) Then
                If sMake Like vStock(lRs, 2) And sGrade Like vStock(lRs, 3) Then
                    ' match found. Add quantity to Inwards column (5th column)
                    vStock(lRs, 5) = vStock(lRs, 5) + vInw(lRi, 4)
                    bFound = True
                    Exit For
                End If
            End If
        Next lRs
        If Not bFound Then  ' bfound=false, so create new row in vStock
            For lRs = UBs To 1 Step -1 ' search for last empty row, going backwards
                If Not vStock(lRs, 1) Like "" Then
                    lRs = lRs + 1 ' increase by one for last empty row
                    Exit For
                End If
            Next lRs
            ' copy data to this row in vStock
            For i = 1 To 3
                vStock(lRs, i) = vInw(lRi, i)
            Next i
            vStock(lRs, 5) = vInw(lRi, 4)
        End If
    Next lRi
    
    'Now write back the adjusted vStock to the Stock sheet
    rStock.Formula = vStock
    
    ' and empty the inward sheet
    Sheets("Inwards").Range("A1").Offset(1, 0).Resize(UBi, UBound(vInw, 2)).ClearContents
    'done!
End Sub
 
Upvote 0
sijpie said:
OK, So you have your form to enter the bearings to the Inward sheet.

Then to transfer them to the stock inventory sheet (after they have been added to the inward sheet) run the following macro.

Read the comments to understand what the macro is doing.

VBA Code: 
Sub ProcessInward()
' Macro that transfers goods from Inwards sheet to Stock Inventory sheet

    Dim vInw, vStock
    Dim lRi As Long, lRs As Long, UBi As Long, UBs As Long
    Dim i As Integer
    Dim sBearing As String, sMake As String, sGrade As String
    Dim bFound As Boolean
    Dim rStock As Range
   
    ' load table in Inwards into array for fast processing
    vInw = Sheets("Inwards").Range("A1").CurrentRegion.Value
    UBi = UBound(vInw, 1) ' store number of rows
   
    ' loadtable in Stock Inventory into array for fast processing. Add sufficient extra rows to add any new items
    With Sheets("Stock Inventory").Range("A7")
        Set rStock = .Resize(.CurrentRegion.Rows.Count + UBi, .CurrentRegion.Columns.Count)
    End With
    vStock = rStock.Formula
    UBs = UBound(vStock, 1) ' store number of rows
   
    ' now go through each row (skip the header) in the inwards array _
      and check if same bearing, grade and make is already present in stock array
    For lRi = 2 To UBi
        'copy the bearing, make and grade into variables  to improve efficiency (removing any whitespace befor or after )
        sBearing = Trim(vInw(lRi, 1))
        sMake = Trim(vInw(lRi, 2))
        sGrade = Trim(vInw(lRi, 3))
        bFound = False
        ' Now loop through each row of vStock to compare (skip header)
        For lRs = 2 To UBs
            If sBearing Like vStock(lRs, 1) Then
                If sMake Like vStock(lRs, 2) And sGrade Like vStock(lRs, 3) Then
                    ' match found. Add quantity to Inwards column (5th column)
                    vStock(lRs, 5) = vStock(lRs, 5) + vInw(lRi, 4)
                    bFound = True
                    Exit For
                End If
            End If
        Next lRs
        If Not bFound Then  ' bfound=false, so create new row in vStock
            For lRs = UBs To 1 Step -1 ' search for last empty row, going backwards
                If Not vStock(lRs, 1) Like "" Then
                    lRs = lRs + 1 ' increase by one for last empty row
                    Exit For
                End If
            Next lRs
            ' copy data to this row in vStock
            For i = 1 To 3
                vStock(lRs, i) = vInw(lRi, i)
            Next i
            vStock(lRs, 5) = vInw(lRi, 4)
        End If
    Next lRi
   
    'Now write back the adjusted vStock to the Stock sheet
    rStock.Formula = vStock
   
    ' and empty the inward sheet
    Sheets("Inwards").Range("A1").Offset(1, 0).Resize(UBi, UBound(vInw, 2)).ClearContents
    'done!
End Sub
Click to expand...
thanks for your guide but code is not run something is mismatch
 

Attachments

  • debug.png
    debug.png
    157.3 KB · Views: 5
  • error in macro.png
    error in macro.png
    169.1 KB · Views: 4
Upvote 0
The macro is trying to add numbers. But apparently one of your columns has text in a field where a number is expected.
 
Upvote 0
sijpie said:
OK, So you have your form to enter the bearings to the Inward sheet.

Then to transfer them to the stock inventory sheet (after they have been added to the inward sheet) run the following macro.

Read the comments to understand what the macro is doing.

VBA Code: 
Sub ProcessInward()
' Macro that transfers goods from Inwards sheet to Stock Inventory sheet

    Dim vInw, vStock
    Dim lRi As Long, lRs As Long, UBi As Long, UBs As Long
    Dim i As Integer
    Dim sBearing As String, sMake As String, sGrade As String
    Dim bFound As Boolean
    Dim rStock As Range
   
    ' load table in Inwards into array for fast processing
    vInw = Sheets("Inwards").Range("A1").CurrentRegion.Value
    UBi = UBound(vInw, 1) ' store number of rows
   
    ' loadtable in Stock Inventory into array for fast processing. Add sufficient extra rows to add any new items
    With Sheets("Stock Inventory").Range("A7")
        Set rStock = .Resize(.CurrentRegion.Rows.Count + UBi, .CurrentRegion.Columns.Count)
    End With
    vStock = rStock.Formula
    UBs = UBound(vStock, 1) ' store number of rows
   
    ' now go through each row (skip the header) in the inwards array _
      and check if same bearing, grade and make is already present in stock array
    For lRi = 2 To UBi
        'copy the bearing, make and grade into variables  to improve efficiency (removing any whitespace befor or after )
        sBearing = Trim(vInw(lRi, 1))
        sMake = Trim(vInw(lRi, 2))
        sGrade = Trim(vInw(lRi, 3))
        bFound = False
        ' Now loop through each row of vStock to compare (skip header)
        For lRs = 2 To UBs
            If sBearing Like vStock(lRs, 1) Then
                If sMake Like vStock(lRs, 2) And sGrade Like vStock(lRs, 3) Then
                    ' match found. Add quantity to Inwards column (5th column)
                    vStock(lRs, 5) = vStock(lRs, 5) + vInw(lRi, 4)
                    bFound = True
                    Exit For
                End If
            End If
        Next lRs
        If Not bFound Then  ' bfound=false, so create new row in vStock
            For lRs = UBs To 1 Step -1 ' search for last empty row, going backwards
                If Not vStock(lRs, 1) Like "" Then
                    lRs = lRs + 1 ' increase by one for last empty row
                    Exit For
                End If
            Next lRs
            ' copy data to this row in vStock
            For i = 1 To 3
                vStock(lRs, i) = vInw(lRi, i)
            Next i
            vStock(lRs, 5) = vInw(lRi, 4)
        End If
    Next lRi
   
    'Now write back the adjusted vStock to the Stock sheet
    rStock.Formula = vStock
   
    ' and empty the inward sheet
    Sheets("Inwards").Range("A1").Offset(1, 0).Resize(UBi, UBound(vInw, 2)).ClearContents
    'done!
End Sub
Click to expand...
I have Some query with these macro coding, whenever macro run in table is disappear and formula also display with "#ref".
 
Upvote 0
Mamtasarkar, if you read through the code then you will see that the last line of code is to clear the inward table. If you put a comment ' in front of the line (or remove it completely) then it won't run.

But that would mean that the inward table keeps growing. Also every time you run the code these items would be added to the stick table again. Probably not what you intend to happen.
 
Upvote 0
sijpie said:
Mamtasarkar, if you read through the code then you will see that the last line of code is to clear the inward table. If you put a comment ' in front of the line (or remove it completely) then it won't run.

But that would mean that the inward table keeps growing. Also every time you run the code these items would be added to the stick table again. Probably not what you intend to happen.
Click to expand...
Thank u for reply sijpie, stock inventory sheet table is disappeared with this formula is also disappear after macro run
 
Last edited:
Upvote 0
mamtasarkar said:
Thank u for reply sijpie, stock inventory sheet table is disappeared with this formula is also disappear after macro run
Click to expand...
I want to my inward sheet table save as it is and transfer data directly to stock inventory sheet without repeat, is it possible.
 
Upvote 0
You must log in or register to reply here.

Similar threads

warhammer486
  • Question
What formulas to use for two workbooks - one referencing the other dynamically but only part of it...??
Replies
3
Views
391
sijpie
sijpie
G
  • Question
Get VBA to automatically generate an email when a cell meets a date condition
Replies
3
Views
228
Logit
Logit
S
  • Question
Cell data from one sheet to another based on drop down box
Replies
3
Views
653
Alex Blakenburg
A
K
  • Solved
My VBA is only copying the last row of data instead of all the rows
2 3
Replies
24
Views
576
Alex Blakenburg
A
B
  • Question
Vba code for matching values
Replies
1
Views
393
brooklyn2007
B

Forum statistics

Threads
1,215,453
Messages
6,124,922
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top