VLOOKUP and Update a Complex Inventory Data

[michaelroshan]

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.
*

MY Excel form looks like below.

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

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