VLOOKUP and Update a Complex Inventory Data

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows
Hi This is my Third post here asking for help again.
I am still struggling to get my inventory management plan fixed and i need help on developing a good management form. i have the following for which i like to use.

I need to Update my inventory by Itemcode, month, and cost Center. the form looks like below.
* As you can see i want to be able to display amounts on the "---" labels. and the three text textboxes is to add to the existing data with add sum.
*

2222Untitled.png


MY Excel form looks like below.

1111Untitled (3).jpg


So As you can see i want to add and edit data by Item Code, Month & Cost Center which is HSK, RHQ, RES, RCG etc... & JAN to DEC.

I am stukc with the following coding which i guess i have managed to mess up bigtime.. I am Lost now..

Please help

VBA Code:
Private Sub CommandButton1_Click()
  Dim rowSelect As Long, colSelect As Long
  
  If MonthBox.Value = "" Then
    MsgBox "Month Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ItemBox.Value = "" Then
    MsgBox "Item Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox2.SetFocus
    Exit Sub
  End If
  
  rowSelect = ItemBox.ListIndex + 5
  'colSelect = ComboBox1.ListIndex * 3 + 4
  Cells(rowSelect, colSelect) = Me.RcvdBox.Value
  Cells(rowSelect, colSelect + 1) = Me.IssueBox.Value

  MsgBox "Data Successfully Updated"
End Sub


Private Sub UserForm_Initialize()
  'Dim i As Long
  Set sh = Sheets("Inventory Data")
  'For i = 1 To 12
  'ComboBox1.AddItem MonthName(i, True)
  With MonthBox
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With

With CCBox
.AddItem "HSK"
.AddItem "RHQ"
.AddItem "RCG"
.AddItem "RES"
End With
  'Next
  ItemBox.List = sh.Range("A5", sh.Range("A" & Rows.Count).End(3)).Value
End Sub

''Private Sub ComboBox1_Change()

'If ItemBox.ListIndex > -1 Then
  'RcvdBox.Value = sh.Cells(ItemBox.ListIndex + 5, 4)
  'IssueBox.Value = sh.Cells(ItemBox.ListIndex + 5, 5)
'End If
'End Sub


'Private Sub ComboBox2_Change()
  'If ComboBox2.ListIndex > -1 Then
    'DescriptionBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 2)
    'CCBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 3)
    'UnitBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 3)
    'BalBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 40)
  'End If
'End Sub

Private Sub CommandButton1_Click()
If Me.MonthBox.Value = "" Then
MsgBox "Month Cannot be Blank!!!", vbExclamation, "Item Code"
Exit Sub
End If

If Me.CCBox.Value = "" Then
MsgBox "Cost Center Cannot be Blank!!!", vbExclamation, "Item Code"
Exit Sub
End If

Dim rowSelect As Double
Dim z As Integer


z = Me.ItemBox.Value
Sheets("Inventory Data").Select

rowSelect = ItemBox.Value
rowSelect = rowSelect + 5
Rows(rowSelect).Select

Cells(rowSelect, 1) = Me.ItemBox.Value
Cells(rowSelect, 2) = Me.NameBox.Value
'Cells(rowSelect, 3) = Me.Label7.Text
If ComboBox1 = "JAN" Then
Cells(rowSelect, 5) = Me.RcvdBox.Value
Cells(rowSelect, 6) = Me.IssueBox.Value
End If
If ComboBox1 = "FEB" Then
Cells(rowSelect, 8) = Me.RcvdBox.Value
Cells(rowSelect, 9) = Me.IssueBox.Value
End If
If ComboBox1 = "MAR" Then
Cells(rowSelect, 11) = Me.RcvdBox.Value
Cells(rowSelect, 12) = Me.IssueBox.Value
End If
If ComboBox1 = "APR" Then
Cells(rowSelect, 14) = Me.RcvdBox.Value
Cells(rowSelect, 15) = Me.IssueBox.Value
End If
If ComboBox1 = "MAY" Then
Cells(rowSelect, 17) = Me.RcvdBox.Value
Cells(rowSelect, 18) = Me.IssueBox.Value
End If
If ComboBox1 = "JUN" Then
Cells(rowSelect, 20) = Me.RcvdBox.Value
Cells(rowSelect, 21) = Me.IssueBox.Value
End If
If ComboBox1 = "JUL" Then
Cells(rowSelect, 23) = Me.RcvdBox.Value
Cells(rowSelect, 24) = Me.IssueBox.Value
End If
If ComboBox1 = "AUG" Then
Cells(rowSelect, 26) = Me.RcvdBox.Value
Cells(rowSelect, 27) = Me.IssueBox.Value
End If
If ComboBox1 = "SEP" Then
Cells(rowSelect, 29) = Me.RcvdBox.Value
Cells(rowSelect, 30) = Me.IssueBox.Value
End If
If ComboBox1 = "OCT" Then
Cells(rowSelect, 32) = Me.RcvdBox.Value
Cells(rowSelect, 33) = Me.IssueBox.Value
End If
If ComboBox1 = "NOV" Then
Cells(rowSelect, 35) = Me.RcvdBox.Value
Cells(rowSelect, 36) = Me.IssueBox.Value
End If
If ComboBox1 = "DEC" Then
Cells(rowSelect, 38) = Me.RcvdBox.Value
Cells(rowSelect, 39) = Me.IssueBox.Value
End If



Private Sub ComboBox2_Change()
Dim y As String
Dim lookupRange As Range
Dim ItemBox As Variant
Set lookupRange = Worksheets("Inventory Data").Range("$A$5:$AN$7000")
If Me.ItemBox.Value = "" Then
MsgBox "Please Enter Item Code to Update!!!", vbExclamation, "Item Code!"
Exit Sub
End If

y = Me.ItemBox.Value

On Error Resume Next
'Me.ComboBox2.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:D7000"), 1, 0)
Me.DescriptionBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:D7000"), 2, 0)
Me.UnitBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:D7000"), 3, 0)
Me.BalBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 39, 0)


If Me.ComboBox1 = "JAN" Then
Me.RcvdBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 4, 0)
Me.IssueBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 5, 0)
End If

If Me.ComboBox1 = "FEB" Then
Me.RcvdBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 7, 0)
Me.IssueBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 8, 0)
End If

If Me.ComboBox1 = "MAR" Then
Me.RcvdBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 10, 0)
Me.IssueBox.Value = Application.WorksheetFunction.VLookup(z, Sheets("Inventory Data").Range("A5:AN7000"), 11, 0)
End If

End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

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
Top