Lookup and Match data

[wiehan1981]

Hi Guys again,

Someone is going to kick me off this forum for asking to much questions.

I am very new at this so please bare with me. I have 2 worksheets. Now Ws 1 stores all employee data. Ws2 has a Userform that looks up an employee from ws1 in one combobox(which I figured out how to get going). The second combobox have to lookup the employees name in cbx1 and match it in ws1 and return the employee ID nr. now this is what I have so far.


Dim ws As Worksheet
Dim aCell As Range
Dim aRow As Long
Set ws = ActiveWorkbook.Worksheets("Shifts")

With ws
'aRow doesn't look right to me
aRow = Application.WorksheetFunction.Match(Int(Me.EmployeeComboBox.Value), Worksheets("Data").Range("empid"), 0)
aRow = iRow + 3

Me.EmplIDbombobox.Value = .Cells(aRow, 2).Value
End With

Please help. I think "aRow" is not specified correctly but have no idea how to fix it.

Thanx guys for putting up with me, please don't kick me off!!!

[CodeNinja]

Hi,
I am not sure you want to use a Combo Box for the second display as they contain lists and I think you are looking for a single answer... anyway, I have attached a file that does what I think you want... You will have to show the userform and you will have to set some variables to your specific sheet, but it does the job... I also have it reporting to a label as that is another option for you. Hopefully this helps.

The code contained is as follows:
Private Sub cbName_Change()
Dim str As String
Dim ws As Worksheet
Dim rng As Range
Dim iColID As Integer
Dim sID As String

icolidoffset = -1
Set ws = ThisWorkbook.Sheets("Shifts")
Set rng = ws.UsedRange

str = cbName.Value
sID = rng.Find(What:=str, Lookat:=xlWhole).Offset(0, icolidoffset)
cbID.Clear
cbID.AddItem (sID)
cbID.Value = sID
lbID.Caption = sID

End Sub

Private Sub UserForm_Initialize()
Dim str As String
Dim i As Integer
Dim ws As Worksheet
Dim istart As Integer
Dim iColName As Integer
Dim sColName As String



sColName = "B"
iColName = 2
istart = 2
Set ws = ThisWorkbook.Sheets("Shifts")

For i = istart To ws.Range(sColName & "65536").End(xlUp).Row
str = ws.Cells(i, iColName)
If Len(Trim(str)) > 0 Then UserForm1.cbName.AddItem (str)
Next i


End Sub


Hope this was of help to you.

[wiehan1981]

Hi,

I have attached the file for better understanding. The reason why I would rather use the combobox is if I have 2 people with the same name but with 2 different IDnr. There is 2 sheets with userforms. the sheet "Shifts" and its user form is my problem.

This code writing is VERY new to me so I still trying to figure some of the stuff out. Sorry to bother you.


Hi,
I am not sure you want to use a Combo Box for the second display as they contain lists and I think you are looking for a single answer... anyway, I have attached a file that does what I think you want... You will have to show the userform and you will have to set some variables to your specific sheet, but it does the job... I also have it reporting to a label as that is another option for you. Hopefully this helps.

The code contained is as follows:
Private Sub cbName_Change()
Dim str As String
Dim ws As Worksheet
Dim rng As Range
Dim iColID As Integer
Dim sID As String

icolidoffset = -1
Set ws = ThisWorkbook.Sheets("Shifts")
Set rng = ws.UsedRange

str = cbName.Value
sID = rng.Find(What:=str, Lookat:=xlWhole).Offset(0, icolidoffset)
cbID.Clear
cbID.AddItem (sID)
cbID.Value = sID
lbID.Caption = sID

End Sub

Private Sub UserForm_Initialize()
Dim str As String
Dim i As Integer
Dim ws As Worksheet
Dim istart As Integer
Dim iColName As Integer
Dim sColName As String



sColName = "B"
iColName = 2
istart = 2
Set ws = ThisWorkbook.Sheets("Shifts")

For i = istart To ws.Range(sColName & "65536").End(xlUp).Row
str = ws.Cells(i, iColName)
If Len(Trim(str)) > 0 Then UserForm1.cbName.AddItem (str)
Next i


End Sub


Hope this was of help to you.

[CodeNinja]

Ok, I think I see what you are doing here... First, its nice work so good for you. Second, I dtPicker is not showing for me, so I cannot run the code... Possibly you are using a datePicker tool that is supported in a different version of excel than I have (the calendar tool is not supported beyond 2007, etc).

You definately want to look at:
aRow = iRow + 3 as iRow is not defined.... do you mean to
to incriment arow by 3... aRow = aRow + 3 and why?

I am not quite sure where/when you are loading employeeComboBox
values into the form either... so you may need to take a look at
that...

as far as finding a match, I think the code I provided for you
earlier will work, but you may have to tweak it for your own
purposes...
sID = rng.Find(What:=str, Lookat:=xlWhole).Offset(0, icolidoffset)

You can use the rng.find method and call it's row value... of course you will have to put it in a while
loop to find multiple matches...



Sorry I cannot be more help, but the wife is calling me... :)

Good luck.

[Tinbendr]

You really should have continued the thread on the other post (http://www.vbaexpress.com/forum/showthread.php?t=42421).

The reason the Match isn't working is because it's trying to look up the Employee ID, not the Employee name.

[wiehan1981]

Thanx for people like you!! I have fixed it up but now I get a runtime error 13 message. This code is the one that you gave to me before. Its works great on the previous userform but not with this one....ARRRGH!!

Any thoughts:


Dim ws As Worksheet
Dim aCell As Range
Dim aRow As Long
Set ws = ActiveWorkbook.Worksheets("Shifts")

With ws
'aRow doesn't look right to me
aRow = Application.WorksheetFunction.Match(Int(Me.EmployeeComboBox.Value), Worksheets("Data").Range("emplname"), 0)
aRow = aRow + 3

Me.ComboBox1.Value = .Cells(aRow, 2).Value
End With


You really should have continued the thread on the other post (http://www.vbaexpress.com/forum/showthread.php?t=42421).

The reason the Match isn't working is because it's trying to look up the Employee ID, not the Employee name.

[CodeNinja]

I dont think me.EmployeeComboBox.Value is an integer... I think that is the employee name, and if you are trying to cast a string into an integer, that would be an issue...