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

M

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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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
 
Upvote 0
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?


DanteAmor said:
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
Click to expand...
 
Upvote 0
michaelroshan said:
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?
Click to expand...


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
 
Upvote 0
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
 
Upvote 0
michaelroshan said:
-> DescriptionBox.Value = sh.Cells(ComboBox2.ListIndex + 5, 2)
Click to expand...
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?
michaelroshan said:
Also i wan to be able to see in the form when i select month
Click to expand...
 
Upvote 0
Hey Man, I am actually lost in all this so maybe i will figure it out later in time..

Thanks Dante! Much appreciated.



DanteAmor said:
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?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
291
Kscoof
K
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
616
Drexi
D
M
  • Question Question
VLOOKUP and Update a Complex Inventory Data
Replies
0
Views
559
michaelroshan
M

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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
Back
Top