trouble with locating a specific cell when using HLookup

[kbishop94]

I need to capture what is in the cell in row 8, and 1 column over from what matches the cboEN value... the cboEN value is determined from a name that the user selects from a dropdown box on a userform.

In the picture that I have as an example, if cboEN = "Bell, Don", then I need to capture (as the assigned ckEMP value shown in the code below) the contents that are in cell AE8... (which is the string "Oni")



My code for this is:

Private Sub cboEN_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Locate EMPLOYEE NAME from ComboBox selection

Dim ckEMP As String

ckEMP = Application.WorksheetFunction.HLookup((Me.cboEN), Object2.Range("DynamicRange"), 8, 0)

If ckEMP = "Oni" Then .chkOninEMP1 = True Else

So what I am trying to make happen is if "Oni" appears in the cell, then a checkbox on the userform that assigned to that value (to the "Oni" value) becomes 'ticked' after the userform is populated based on the cboEN value (which in this case is "Bell, Don" in row 1 on the worksheet "Object2")

Lastly, DynamicRange covers a large area on the object2 worksheet beginning in Row 1... which is the row that contains the names that cboEN is matching up to. (hope that makes sense! )

Thanks for any help or assistance from anyone willing to help!

 

[Fluff]

How about

Dim ckEMP As Range

Set ckEMP = Object2.Range("DynamicRange").Find(Me.cboEN, , , xlWhole, , , False, , False)
If ckEMP Is Nothing Then Exit Sub
If ckEMP.Offset(7).Value = "Oni" Then .hkOninEMP1 = True

 

[kbishop94]

How about

Dim ckEMP As Range

Set ckEMP = Object2.Range("DynamicRange").Find(Me.cboEN, , , xlWhole, , , False, , False)
If ckEMP Is Nothing Then Exit Sub
If ckEMP.Offset(7).Value = "Oni" Then .hkOninEMP1 = True

Thank you, Fluff.

Unfortunately that didnt work.

First I thought it might be how that cell (the one containing "Oni") might be formatted. I checked, and it was a date format. I changed it to General, then to Text. Both didn't change the result.

Next I tried a couple different numbers for the offset number... you had (7) which was in your code- but each time it still didnt work or produce any different result.

Also, the code runs without any error, but it just doesnt give the correct return for what the ckEMP value should be... (which in the example I am using having the cboEN value equal "Bell, Don", it should be "Oni".)

So as a last resort, I tried having it show me what was actually getting returned as the value of "ckEMP". I added "MsgBox ckEMP" to the code. Each time the messagebox returns the value of cboEN (again, in this case, the name "Bell, Don".) It returns the same thing regardless what I use for the ".Offset" number (I tried both (7) and (8)... both results with the MsgBox showing me the name "Bell, Don".)

Any suggestions or ideas?

Thank you again, Fluff.
-Keith

 

[Fluff]

I added "MsgBox ckEMP" to the code. Each time the messagebox returns the value of cboEN (again, in this case, the name "Bell, Don".)

That's exactly what it should do.

What does this message box return

Set ckEMP = Object2.Range("DynamicRange").Find(Me.cboEN, , , xlWhole, , , False, , False)
If ckEMP Is Nothing Then Exit Sub
MsgBox "|" & ckEMP.Offset(7).Value & "|"
If ckEMP.Offset(7).Value = "Oni" Then .hkOninEMP1 = True

 

[kbishop94]

That's exactly what it should do.

Oh, ok.


When I added:

MsgBox "|" & ckEMP.Offset(7).Value & "|"

It returned cell "AD8"

( target cell is AE8.)

Pic:

 

[Fluff]

Oops, forgot to move one column, it should be

If ckEMP.Offset(7, 1).Value = "Oni" Then .hkOninEMP1 = True

 

[kbishop94]

Oops, forgot to move one column, it should be

If ckEMP.Offset(7, 1).Value = "Oni" Then .hkOninEMP1 = True

As always, thank you, Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.