VBA userform to vlookup from another sheet

June 11, 2019 at 06:35:04
Specs: Windows 10
Hi Humar,

Reading your responses to this I feel you may the man to help me.

I am trying to get a label (label5) to lookup from the number that is entered into reg1, however I want the lookup to find data from worksheet.consumables.

Ideally after this has been found I would like it to turn red if the number found is negative.

Private Sub Reg1_AfterUpdate()
 'Check to see if value exists
 If WorksheetFunction.CountIf(Sheet28.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
' .reg1 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 1, 0)
' .reg2 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 2, 0)
' .reg3 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 3, 0)
 '.reg4 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 4, 0)
 '.reg5 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 5, 0)
 '.reg6 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 6, 0)
 .reg4 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet28.Range("LOOKUP"), 7, 0)
' .reg8 = Application.WorksheetFunction.VLookup(CLng(Me.reg1), Sheet5.Range("LOOKUP"), 8, 0)
 
 

 End With
 
End Sub

Hope this makes sense and thanks in advance.

Joe


See More: VBA userform to vlookup from another sheet

Reply ↓  Report •
Related Solutions


Ask Question