having trouble understanding how(why) its finding this one cell when using VLookup in a userform(?)

[kbishop94]

I have a userform with a combobox to select names on a worksheet. The names and associated data is located in a named range (DynamicRange)

Below are some screen shots and the code that I curerntly have:

my userform showing where I am choosing the name "Aimes, Kelly"

:



and after I leave that combobox (cboEN is the name of the combobox)

I want it to be able to find that name here:

So here is my code:

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

If WorksheetFunction.CountIf(Object2.Range("Names"), Me.cboEN.Value) = 0 Then
MsgBox "Employee Not Found."
Me.cboEN.Value = ""
Cancel = True
Exit Sub
End If

With Me

cK1 = Application.WorksheetFunction.VLookup((Me.cboEN), Object2.Range("DynamicRange"), 1, 2)
If cK1 = "b" Then .chkNew1 = True

MsgBox cK1
MsgBox cboEN

End With
End Sub

Now, I know its working, because I can confirm what its finding:
it finds 'a' which is row 4 in the column that the name "Aimes, Kelly" is in, and its also finding the correct name ("Aimes, Kelly") (combined 2 screen shots so you can see both message boxes):

So what I cant figure out is why it seems no matter what I change these numbers to (in red):

cK1 = Application.WorksheetFunction.VLookup((Me.cboEN), Object2.Range("DynamicRange"), [B][COLOR=#ff0000]1, 2[/COLOR][/B])
If cK1 = "b" Then .chkNew1 = True

I only seem to get "a" or "b"... ??

All the other times I use this method of VLookup, I always have the data I am trying to retrieve its in ROWS, and this time the data associated with my lookup is in columns (2 columns at that... the one directly below the name, and the column immediately to the right of the name column.)

For instance, lets say I want to retrieve the "R" that is 4 rows directly below the name "Aimes, Kelly" ("R" is located in column N and row 5 and I have it oulined in GREEN in the screenshot below)
What would I have to use to find this location?

Please and Thank you for any help you guys can offer me.

 

[kbishop94]

This is probably useful information as well:

Here is the range of my dynamic range (aptly named) "DynamicRange".
It starts at column H and goes down to the bottom (row 63) and over to the last column (which is currently PE)

 

[kbishop94]

still trying to get this figured out... anyone? Thanks

 

[Akuini]

For instance, lets say I want to retrieve the "R" that is 4 rows directly below the name "Aimes, Kelly" ("R" is located in column N and row 5 and I have it oulined in GREEN in the screenshot below)
What would I have to use to find this location?

Maybe this way :

a = Application.Match("Aimes, Kelly", Object2.Range("DynamicRange").Rows(1), False)
txt = Object2.Range("DynamicRange").Cells(5, a)

You'll find that txt = "R"

 

[kbishop94]

Maybe this way :

a = Application.Match("Aimes, Kelly", Object2.Range("DynamicRange").Rows(1), False)
txt = Object2.Range("DynamicRange").Cells(5, a)

You'll find that txt = "R"

Thanks, Akuini.

However, I get this error:

Since row 4 is all either 'a' 's or 'b' 's, what about finding the specific 'a' that its findind and illuminating that specific cell on the sheet? (because the entire row 4 is either an 'a' or 'b')

What can i do to have it do to find SOMETHING on that sheet that can serve as a reference to pinpoint where it is on the sheet and then I can work from that to locate a specific cell or range?

I thought this would locate and show me 'cK1', but I cant seem to see what its highlighting... (?)

Range("cK1").Interior.Color = 65280

Any suggestions?

Thank you

 

[Fluff]

Whilst I haven't a clue what you are trying to do, you are using vertical lookup, when the names are in a row, try

cK1 = Application.WorksheetFunction.HLookup((Me.cboEN), Object2.Range("DynamicRange"), 5, 0)