VLOOKUP inventory details using Alphanumeric Serial Numbers, and update it in the same line.

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows
Dear Friends,
I am working on a Form to retrieve data on an Inventory data sheet using the serial numbers that look like this "CL/SBR/08" the item count is around 3000 and on a daily basis i need to update incoming and out going stock based on the serial number.

(1) i need to be able to retrieve data to a userform entering this serial# to a combobox or textbox and retrieve the details.

(2) then i want to change the values on the data in the same form and click update, so that the edited data will be updated on the same row that has the serial# in the sheet.

I am not a VBA expert and i have no experience at all! so i gather coding from google, put them together and make this.

the coding i have is below! please help me to organise this and get it running. THANKS A MILLION.

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

Dim rowselect As Double
Dim z As Integer


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

rowselect = ComboBox2.Value
rowselect = rowselect + 5
Rows(rowselect).Select

'Cells(rowselect, 1) = Me.ComboBox2.Value
'Cells(rowselect, 2) = Me.DescriptionBox.Value
'Cells(rowselect, 3) = Me.CCBox.Value

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


rowselect = rowselect - 1
msg = "Data Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
FormInvUpdate.Show
Else
Sheets("Inventory Data").Select
End If
End Sub


Private Sub UserForm_Initialize()
With ComboBox1
.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
End Sub

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

z = Me.ComboBox2.Text
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.CCBox.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:d7000"), 40, 0)[/B][/COLOR]

End Sub

Untitled1.jpg
[/COLOR][/B]
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Option Explicit         'At the beginning of all the code
Dim sh As Worksheet

Private Sub CommandButton1_Click()
  Dim rowSelect As Long, colSelect As Long
  
  If ComboBox1.Value = "" Then
    MsgBox "Month Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ComboBox2.Value = "" Then
    MsgBox "Item Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox2.SetFocus
    Exit Sub
  End If
  
  rowSelect = ComboBox2.ListIndex + 5
  colSelect = ComboBox1.ListIndex * 3 + 5
  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)
  Next
  ComboBox2.List = sh.Range("A5", sh.Range("A" & Rows.Count).End(3)).Value
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)
    BalBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 40)
  End If
End Sub
 

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows
hi,

Many Thanks for the response and much appreciated.

However in the following code the highlited green gives and error saying Object required :unsure:!

What should i do to that?



Try this:

VBA Code:
Option Explicit         'At the beginning of all the code
Dim sh As Worksheet

Private Sub CommandButton1_Click()
  Dim rowSelect As Long, colSelect As Long
 
  If ComboBox1.Value = "" Then
    MsgBox "Month Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If ComboBox2.Value = "" Then
    MsgBox "Item Cannot be Blank!!!", vbExclamation, "Item Code"
    ComboBox2.SetFocus
    Exit Sub
  End If
 
  rowSelect = ComboBox2.ListIndex + 5
  colSelect = ComboBox1.ListIndex * 3 + 5
  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)
  Next
  ComboBox2.List = sh.Range("A5", sh.Range("A" & Rows.Count).End(3)).Value
End Sub

Private Sub ComboBox2_Change()
  If ComboBox2.ListIndex > -1 Then
[B][COLOR=rgb(0, 168, 133)]    [/COLOR][COLOR=rgb(184, 49, 47)]DescriptionBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 2)[/COLOR][/B]
    CCBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 3)
    BalBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 40)
  End If
End Sub
 

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows
hi,

Many Thanks for the response and much appreciated.

However in the following code the highlited green gives and error saying Object required :unsure:!

What should i do to that?


Sorry this following code :

Private Sub ComboBox2_Change()
If ComboBox2.ListIndex > -1 Then
-> DescriptionBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 2)[/COLOR][/B]
CCBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 3)
BalBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 40)
End If
 

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Also i wan to be able to see in the form when i select month (which is combo box 1), the values i have already entered therebefore! in the following code..
My spread sheet has two designated colomn for each (Rcvd & Issue) for each month.

VBA Code:
Private Sub UserForm_Initialize()
  Dim i As Long
  Set sh = Sheets("Inventory Data")
  For i = 1 To 12
    ComboBox1.AddItem MonthName(i, True)
  Next
  ComboBox2.List = sh.Range("A5", sh.Range("A" & Rows.Count).End(3)).Value
End Sub


I tried the following but it dosnt work! :cry:


VBA Code:
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
-> DescriptionBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 2)
According to your code, you have a textbox called "DescriptionBox". Check that you have that textbox.

Also check that these lines are at the beginning of all the code
VBA Code:
Option Explicit         'At the beginning of all the code
Dim sh As Worksheet

We finish the above and then continue with this part, do you agree?
Also i wan to be able to see in the form when i select month
 

michaelroshan

New Member
Joined
Jun 27, 2020
Messages
16
Office Version
  1. 2007
Platform
  1. Windows
Hey Man, I am actually lost in all this so maybe i will figure it out later in time..

Thanks Dante! Much appreciated.



According to your code, you have a textbox called "DescriptionBox". Check that you have that textbox.

Also check that these lines are at the beginning of all the code
VBA Code:
Option Explicit         'At the beginning of all the code
Dim sh As Worksheet

We finish the above and then continue with this part, do you agree?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I am actually lost in all this so maybe i will figure it out later in time..
You just had to copy the entire code and replace your code. But you were the one who asked for help. Good luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,550
Messages
5,625,448
Members
416,107
Latest member
AVaes

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