Need help with constructing a vba code, please!

[estatefinds]

I have 5 number combinations in column A that is separated by "-".
I need a macro in which I select a combination, and the macro returns the location of each of the FIVE numbers of that selected combination, and places the results into columns B thru F.

So for example: the 17 in the combination selected in the black box border is found below this selected combination three rows down in the third position which I call C but is actually found on the excel column D So I place a 3 there.

then the next number 19 is found 8 rows down from the combination selected and in the third position called C which is the excel column D and the number 8 is placed there.

then the number 27 is found nine rows down from the selected combination and in the fifth position called E which is the excel column F a 9 is placed there.

then the number 28 is found is six rows down from the selected combination and in the fifth postion called E which is the excel position F and a 6 is placed there.


then the number 31 is found ten rows below the selected combination in the position called D in which the excel column is E and the number 10 placed there.

When I go down to select the next combination the data in columns H thru L will go down one row as well this is just a visual aid.and the results will follow the combination I select. so the data result will line up with each combination I select just as the first example I posted.

now, when I select the the next combination down the invisible chart H thru L just for show shows how I come up with how many rows down from the combination.

Also the data in N thru R is also just to visualize how this works just a visual aid only.

Any help at all is appreciated!!! Thank you in advance!

[mikerickson]

Since 17 is the first number in the indicated cell ($A$9) and column B is the "A" column.

Why is the 3 in cell $D$12 and not $B$12.

Edit: never mind, I figured out that the column is dependent on where it is in the lower matching cell.

[mikerickson]

I think this will do what you want. Select the cell in question and run this macro.

Sub test()
    Dim keyCell As Range
    Dim foundRight As Range, foundLeft As Range
    Dim writeCell As Range
    Dim Numerals As Variant, i As Long
    
    Set keyCell = Selection.Cells(1, 1)
    Numerals = Split(CStr(keyCell.Value), "-")
    keyCell.Offset(0, 1).Resize(1000, 5).ClearContents
    For i = 0 To UBound(Numerals)
        With keyCell
            With Range(.Cells(2, 1), .EntireColumn.Cells(Rows.Count, 1))
                Set foundRight = .Find(Numerals(i) & "-", after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)
                Set foundLeft = .Find("-" & Numerals(i), after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext)
            End With
        End With
        If foundRight Is Nothing And foundLeft Is Nothing Then
            Set writeCell = Nothing
        ElseIf foundRight Is Nothing Then
            Set writeCell = foundLeft
        ElseIf foundLeft Is Nothing Then
            Set writeCell = foundRight
        Else
            Set writeCell = foundRight
            If foundLeft.Row < writeCell.Row Then Set writeCell = foundLeft
        End If
        
        If Not writeCell Is Nothing Then
            With writeCell
                .Cells(1, Application.Match(Numerals(i), Split(.Value, "-"), 0)).Value = writeCell.Row - keyCell.Row
            End With
        End If
    Next i
End Sub

[estatefinds]

ok it worked on the first one great!

then when i went down to the next combination down ran it,
and the third combination down from the 17-19-27-28-31 was cleared and replaced with a 2

[estatefinds]

But also if the number is found in A it has to be placed under the column B other wise it will interfere with combinations in Column A

[estatefinds]

so the macro is looking at the columns A B C D E as they are
.
For example, Select the combination 2-5-9-10-34 and you see what happends.
the macro has to look at the actaul columns B C D E F as the A B C D E.

[mikerickson]

I re-wrote the search routine and the writing routine.

Sub test()
    Dim keyCell As Range
    Dim SearchRange As Range
    Dim writeCell As Range, oneCell
    Dim Numerals As Variant, i As Long

    If Selection.Column <> 1 Then Beep: Exit Sub

    Set keyCell = Selection.Cells(1, 1)
    Numerals = Split(CStr(keyCell.Value), "-")
    With keyCell
        Set SearchRange = Range(.Cells(2, 1), .EntireColumn.Cells(Rows.Count, 1).End(xlUp))
    End With
    SearchRange.Offset(0, 1).Resize(, 5).ClearContents

    For i = 0 To UBound(Numerals)
        Set writeCell = Nothing
        For Each oneCell In SearchRange
            If IsNumeric(Application.Match(Numerals(i), Split(oneCell.Value, "-"), 0)) Then
                Set writeCell = oneCell
                Exit For
            End If
        Next oneCell
        
        If Not writeCell Is Nothing Then
            With writeCell
                .Offset(0, Application.Match(Numerals(i), Split(.Value, "-"), 0)).Value = writeCell.Row - keyCell.Row
            End With
        End If
    Next i
End Sub

[estatefinds]

IT WORKS GREAT THANK YOU!!!!