Tanyaann1995
Board Regular
- Joined
- Mar 24, 2021
- Messages
- 62
- Office Version
- 2016
- Platform
- Windows
Hi,
I have a list of part numbers in Column D in a workbook. I need the code to check the stock for each part number by selecting each part number and then checking for the stock in another file. Once it gets the stock quantity, I need a message box to pop up saying that this item has .... pieces in stock. I have developed below code for it:
VBA Code:
Sub pdc()
Dim i As Integer
Dim f As Workbook
Dim pno As String
Dim lastrow As Long
Dim Sum As Integer
Dim rng As Range
lastrow = ThisWorkbook.Worksheets(2).Range("D:D").Find(What:="*", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
For i = 24 To lastrow
pno = ThisWorkbook.Worksheets(2).Cells(i, 4).Value
Set f = Workbooks.Open("\\emrsn.org\VC-Drive_N\AEDU1_QUOTATIONS\Quotes FY 2021\MRO\TANYA\3 PRICING-SN-PN TOOLS\JDI_PDC_Onhand_P08 End.xlsx", True, True)
f.Worksheets(1).Range("A1:H422").AutoFilter Field:=1, Criteria1:="=*" & pno & "*"
If f.Worksheets(1).AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
With f.Worksheets(1)
Set rng = .Range(.Cells(2, 6), .Cells(.Rows.Count, 13).End(xlUp))
End With
Sum = Application.WorksheetFunction.Subtotal(109, rng)
MsgBox pno & " - " & Sum & " pcs"
Else
MsgBox pno & " - " & "No stock in PDC"
End If
Next i
End Sub
This code will display a message box for each part number but I want to display 1 message box with all the part numbers and its corresponding stock quantity. How do i change the code in that case?
I have a list of part numbers in Column D in a workbook. I need the code to check the stock for each part number by selecting each part number and then checking for the stock in another file. Once it gets the stock quantity, I need a message box to pop up saying that this item has .... pieces in stock. I have developed below code for it:
VBA Code:
Sub pdc()
Dim i As Integer
Dim f As Workbook
Dim pno As String
Dim lastrow As Long
Dim Sum As Integer
Dim rng As Range
lastrow = ThisWorkbook.Worksheets(2).Range("D:D").Find(What:="*", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
For i = 24 To lastrow
pno = ThisWorkbook.Worksheets(2).Cells(i, 4).Value
Set f = Workbooks.Open("\\emrsn.org\VC-Drive_N\AEDU1_QUOTATIONS\Quotes FY 2021\MRO\TANYA\3 PRICING-SN-PN TOOLS\JDI_PDC_Onhand_P08 End.xlsx", True, True)
f.Worksheets(1).Range("A1:H422").AutoFilter Field:=1, Criteria1:="=*" & pno & "*"
If f.Worksheets(1).AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
With f.Worksheets(1)
Set rng = .Range(.Cells(2, 6), .Cells(.Rows.Count, 13).End(xlUp))
End With
Sum = Application.WorksheetFunction.Subtotal(109, rng)
MsgBox pno & " - " & Sum & " pcs"
Else
MsgBox pno & " - " & "No stock in PDC"
End If
Next i
End Sub
This code will display a message box for each part number but I want to display 1 message box with all the part numbers and its corresponding stock quantity. How do i change the code in that case?