Use Case Select for inventory in Excel

Let me teach you the VBA that I have learn in my five years of consulting

Become an Affiliate and earn 25% on Course Sales

For more help visit my website or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting

I am able to provide online help on your computer at a reasonable rate.

Check out my next one-hour Excel Webinar

I use a Blue condenser Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

If you need to buy Office 2019 follow

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.

Sub selectlist()
Dim Prodnum As String
Dim start As Range
Dim r, c As Long
r = 7
c = 3
Sheet2.Activate
Sheet2.Cells(r, c).Select

Do While Sheet2.Cells(r, c).Value not equal to “”
Prodnum = Sheet2.Cells(r, c).Value

Select Case Prodnum
Case Is = “R101”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“B8”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If
Case “R105”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“I8”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If
Case “R102”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“B41”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If

Case “R103”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“B71”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If

Case Is = “R104”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“I71”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If

Case Is = “R106”
If Sheet2.Cells(r, c – 1) not equal to “Done” Then
Set start = Sheet6.Range(“I41”)
Sheet2.Cells(r, c – 1).Value = “Done”
Call removeinventory(start)
Else
GoTo ende
End If

End Select
ende:
r = r + 1
Loop

End Sub

Sub removeinventory(start)
Dim r, c As Long
Dim part As Range
Dim prod As String
Dim qty, rownumber As Long
Dim usedqty As Long

Sheet6.Activate
c = start.Column
r = start.Row
Do While Sheet6.Cells(r, c) not equal to “”
Sheet6.Cells(r, c).Select
prod = Sheet6.Cells(r, c).Value
qty = Sheet6.Cells(r, c).Offset(0, 2).Value
Sheet1.Activate
‘search for partnumber
Set part = Sheet1.Columns(“B:B”).Find(What:=prod, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If part Is Nothing Then
MsgBox att & “not found”
GoTo ende
Else
rownumber = part.Row
End If
‘add qty to used

Sheet1.Cells(rownumber, 6).Value = Sheet1.Cells(rownumber, 6).Value + qty
prod = “”
qty = “”
ende:
Sheet6.Activate
r = r + 1
Loop
Sheet2.Activate
End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English