I have a userform where once i keyin the part no(textbox1),description(textbox 2) and supplier(Textbox3) will auto populate. And when i click the update button after keyin Date(textbox4) & Quantity(textbox5) the data's will be updated in worksheet4. For example the data's will be updated in cell A2,B2,C2,D2 & E2 and the next update will be in A3,B3,C3,D3 & E3. and this goes on. This is working fine.
Im stucked where, I have some datas in Worksheet5. Column A part numbers and column B descriptions.
Now i want the value in Quantity(textbox5) to be updated in worksheet5 Column C depends on the value on part no(textbox1)
once i click the update button in my userform. And the following update on the same part no must go into the next column.
Please advice,
Below is my userform and codings.
[/IMG]
Im stucked where, I have some datas in Worksheet5. Column A part numbers and column B descriptions.
Now i want the value in Quantity(textbox5) to be updated in worksheet5 Column C depends on the value on part no(textbox1)
once i click the update button in my userform. And the following update on the same part no must go into the next column.
Please advice,
Below is my userform and codings.

Code:
Option Explicit
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdUpdate_Click()
'--------------------------------------
' Copy values in textboxes into excel sheet
'--------------------------------------
Dim x As Integer
Dim nextrow As Range
'to check empty textboxes
Set nextrow = Sheet4.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
For x = 1 To 5
If Me.Controls("stock" & x).Value = "" Then
MsgBox "Missing data"
Exit Sub
End If
Next
'add values if previous criteria matches
For x = 1 To 5
nextrow = Me.Controls("stock" & x).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'To clear textboxes
For x = 1 To 5
Me.Controls("stock" & x).Value = ""
Next
Exit Sub
End Sub
Private Sub Stock1_Change()
'--------------------------------------
' Check database for entry in Textbox1 _
and if in DB then populate other TB _
'--------------------------------------
Dim rFound As Range
Dim wsData As Worksheet
Dim sID As String
Dim j As Integer
Worksheets("Stock Update").Activate
Set wsData = Sheets("Stock Update")
With wsData
' Get the item number in sID
sID = Me.Stock1.Value
' check column A of the datasheet for the entry
Set rFound = Columns("A").Find(what:=sID, _
after:=.Cells(1, 1))
' if found, process. else quit
If Not rFound Is Nothing Then ' This checks that rFound is set to an object _
and not 'nothing'
' Load the details in the text boxes
For j = 2 To 3
Me.Controls("stock" & j).Value = rFound.Offset(0, j - 1).Value
Next j
End If
End With
End Sub
Private Sub UserForm_Initialize()
Stock1.SetFocus
End Sub