[Solved] Need help to track stock

anuragv

New member
Dear All,
In the example file, there are 3 spreadsheets. The "Lot no. 1" & "Lot no. 2" sheets are used to enter all the information to manage inventory & as you can see, some items are not dispatch so the cell value is blank.
Now, Can anyone please tell me a formula or macro from which i can scan both spreadsheets with blank "Invoice no."or "Dispatch date" columns and if the condition is true then copy all the information in the adjacent row to stock spreadsheet.
Condition I'm looking for is: If a cell in either "Invoice no."or "Dispatch date" column is blank then copy all the information in the adjacent row of "Lot" Sheets to a row below the headings in "Stock" spreadsheet. If the cell is not blank then search for next cell and copy the info if true but it should not leave a blank row in stock spreadsheet. Suppose there are 25+ grades of each material, we can't just write all the grades in stock table & paste the stock formula for quantity. Which is why, the formula should look for all the blank value & keep pasting the info in the stock table.
Is this possible in excel? Can this be done with Vlookup, if, isblank & loop functions?
 

Attachments

  • example.xlsx
    11.6 KB · Views: 3
Dear All,
In the example file, there are 3 spreadsheets. The "Lot no. 1" & "Lot no. 2" sheets are used to enter all the information to manage inventory & as you can see, some items are not dispatch so the cell value is blank.
Now, Can anyone please tell me a formula or macro from which i can scan both spreadsheets with blank "Invoice no."or "Dispatch date" columns and if the condition is true then copy all the information in the adjacent row to stock spreadsheet.
Condition I'm looking for is: If a cell in either "Invoice no."or "Dispatch date" column is blank then copy all the information in the adjacent row of "Lot" Sheets to a row below the headings in "Stock" spreadsheet. If the cell is not blank then search for next cell and copy the info if true but it should not leave a blank row in stock spreadsheet. Suppose there are 25+ grades of each material, we can't just write all the grades in stock table & paste the stock formula for quantity. Which is why, the formula should look for all the blank value & keep pasting the info in the stock table.
Is this possible in excel? Can this be done with Vlookup, if, isblank & loop functions?
Hello Anuragv,

The issue you are addressing can be solved using the VLOOKUP, IF and ISBLANK functions. However, Excel VBA provides us with a prominent solution regarding your issue.

Regards
 
Dear All,
In the example file, there are 3 spreadsheets. The "Lot no. 1" & "Lot no. 2" sheets are used to enter all the information to manage inventory & as you can see, some items are not dispatch so the cell value is blank.
Now, Can anyone please tell me a formula or macro from which i can scan both spreadsheets with blank "Invoice no."or "Dispatch date" columns and if the condition is true then copy all the information in the adjacent row to stock spreadsheet.
Condition I'm looking for is: If a cell in either "Invoice no."or "Dispatch date" column is blank then copy all the information in the adjacent row of "Lot" Sheets to a row below the headings in "Stock" spreadsheet. If the cell is not blank then search for next cell and copy the info if true but it should not leave a blank row in stock spreadsheet. Suppose there are 25+ grades of each material, we can't just write all the grades in stock table & paste the stock formula for quantity. Which is why, the formula should look for all the blank value & keep pasting the info in the stock table.
Is this possible in excel? Can this be done with Vlookup, if, isblank & loop functions?
Dear Anuragv

Thanks for reaching out and posting your query on this platform. After investigating the issue you mentioned, I understand that you want a formula or VBA macro to scan both spreadsheets with blank Invoice no or Dispatch date columns, and if the condition is true, copy all the information in the adjacent row to the stock spreadsheet. Per request, I have developed an Excel VBA code that fulfils your goal.

Excel VBA Code:

Code:
Sub SolutionToTrackStock()

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long, destRow As Long
    Dim i As Long
   
    Set ws1 = ThisWorkbook.Sheets("Lot no. 1")
    Set ws2 = ThisWorkbook.Sheets("Lot no. 2")
    Set ws3 = ThisWorkbook.Sheets("Stock")
   
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
   
    destRow = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row + 1
   
    For i = 4 To lastRow1
       
        If ws1.Cells(i, "F").Value = "" Or ws1.Cells(i, "G").Value = "" Then
           
            ws1.Rows(i).Copy ws3.Rows(destRow)
            destRow = destRow + 1
       
        End If
       
    Next i
   
    For i = 6 To lastRow2
       
        If ws2.Cells(i, "F").Value = "" Or ws2.Cells(i, "G").Value = "" Then
           
            ws2.Rows(i).Copy ws3.Rows(destRow)
            destRow = destRow + 1
       
        End If
       
    Next i

End Sub

Please get in touch with us if you have any additional Excel-related questions or problems. We are here to assist you with any Excel-related concerns you may encounter.

Regards
Lutfor Rahman Shimanto
 

Attachments

  • example(Solved).xlsm
    19 KB · Views: 6
Dear Luftor,
Apologies for the delay. This is to inform you that you are a ROCKSTAR. I tried the code & it does exactly what I was trying to do. Also I added ----Worksheets("Stock").Range("start of table", "P999").Delete---- at the start of code so that on each run it will act as refresh on stock page deleting previous entries & replacing them with new ones.

Thank you very much.
 
Dear Luftor,
Apologies for the delay. This is to inform you that you are a ROCKSTAR. I tried the code & it does exactly what I was trying to do. Also I added ----Worksheets("Stock").Range("start of table", "P999").Delete---- at the start of code so that on each run it will act as refresh on stock page deleting previous entries & replacing them with new ones.

Thank you very much.
Dear Anuragv,

You're very most welcome! I'm glad to hear the VBA code worked for you. We are always happy to help, and it makes us happy that we can make your life easier.

Getting such a touching thankfulness inspires us to keep helping people. Don't hesitate to contact us with further questions or assistance.

Regards,
Lutfor Rahman Shimanto
 

Online statistics

Members online
0
Guests online
8
Total visitors
8

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top