Selecting column heading based on cell selection

[WayneAli]

Hi all, back again.
I have a form with a combobox on a userform populated with staff names. I'm trying to populate a series (smallest first) of label.captions based on which name is selected in the combobox.

The staff name is identified as MOKr, which finds their name in speadsheets("MOK eLearns") and then searches that row of data for the smallest value and returns that figure to label110.caption, then searches for the second smallest and returns to label111.caption, third smallest to label113.caption, etc. Then, and this is where I am coming unstuck, I want to use the column address of that "lowestcell" to select the corresponding course name in row 4.

I have got it searching through for the first to tenth lowest cells and returning their values to label110 to label119 correctly, however, when it comes to selecting the column title (row 4) it is returning the same value if the search values are the same.

eg:

Days Course title
remaining
4 AFA Domestic
4 AFA Domestic
20 Basement Fires
36 Chimney Fires
154 Fires in the Open
154 Fires in the Open
158 Derelict Property

so in this case, the first 4 is correct with AFA Domestic identified as the course title, however the second should be selecting AFA Generic which also has 4 days remaining. The same with Fires in the Open, the second should be selecting Stack Fires.

Here is the code I have so far, I did have it set up in a loop with j = 0 to 9 step 1 but have separated it just for ease of tweaking.

Private Sub ComboBox1_Change()
Dim crew_Name, MOKr As Variant
Dim MOKws As Worksheet
Dim lowestCell As Range, crewRange As Range

crew_Name = Me.ComboBox1.Value
    If Len(crew_Name) = 0 Then Exit Sub
Set MOKws = ThisWorkbook.Worksheets("MOK eLearns")
MOKr = Application.Match(crew_Name, MOKws.Range("A6:BC25").Columns(4), 0)
    If IsError(MOKr) Then Err.Raise 744, , crew_Name & " Not Found"
Set crewRange = MOKws.Range(Cells(CLng(MOKr) + 5, 5), Cells(CLng(MOKr) + 5, 70))

For Each lowestCell In crewRange

If lowestCell.Value = Application.WorksheetFunction.Small(crewRange, 1) Then
Controls("label110").Caption = lowestCell.Value
Controls("label210").Caption = Cells(4, CLng(lowestCell.Column)).Value
End If

If lowestCell.Value = Application.WorksheetFunction.Small(crewRange, 2) Then
Controls("label111").Caption = lowestCell.Value
Controls("label211").Caption = Cells(4, CLng(lowestCell.Column)).Value
End If

If lowestCell.Value = Application.WorksheetFunction.Small(crewRange, 3) Then
Controls("label112").Caption = lowestCell.Value
Controls("label212").Caption = Cells(4, CLng(lowestCell.Column)).Value
End If

'-------continue on to...

If lowestCell.Value = Application.WorksheetFunction.Small(crewRange, 10) Then
Controls("label119").Caption = lowestCell.Value
Controls("label219").Caption = Cells(4, CLng(lowestCell.Column)).Value
End If

Next lowestCell
End Sub

I think, instead of just inputting the lowestcell.value to the label caption, if I could select that lowestcell and grab the column number I could use this to get the correct corresponding course title.

I hope this makes sense.

 

[WayneAli]

Missed my 10 minutes to edit so have just added this to show the example a bit clearer

eg:

so in this case, the top 5 are correct as they have different values in "Days Remaining", however when the value 87 is found, it is returning Fire - Cylinder for all 3 when it should be returning Fire - Cylinder, Fire - Industrial and Basement Fires. The same with 121 which should be Single Lane Carriageways and then Multi Lane Carriageways