Hello there,
I have this code that is filling my sheet3 with VLOOKUP formula. That's great. But i'd like to to put a number value before that ( Like a quantity number ).
Right now the code fills with data from Column C to Column H, i'd like to create a textbox in my userform to register the number value ( 1 to 99, for example ) in Column B without the need of VLOOKUP, pure and simple a quantity number in a textbox.
I have this code that is filling my sheet3 with VLOOKUP formula. That's great. But i'd like to to put a number value before that ( Like a quantity number ).
Right now the code fills with data from Column C to Column H, i'd like to create a textbox in my userform to register the number value ( 1 to 99, for example ) in Column B without the need of VLOOKUP, pure and simple a quantity number in a textbox.
VBA Code:
Private Sub cmdSend_Click()
'Dim the variables
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
'change the number for the number of controls on the userform
cNum = 6
Set nextrow = Sheet3.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Cells(nextrow, 2) = reg7.Value
Next
MsgBox "The data has been sent"
'Clear the controls
cNum = 6
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub
Private Sub Reg1_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.reg1.Value) = 0 Then
MsgBox "This is an incorrect ID"
Me.reg1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.reg2 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 2, 0)
.reg3 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 3, 0)
.reg4 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 4, 0)
.reg5 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 5, 0)
.reg6 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet2.Range("Lookup"), 6, 0)
End With
End Sub