PDA

View Full Version : [SOLVED] Need help with constructing a vba code, please!



estatefinds
07-09-2016, 03:10 PM
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
07-09-2016, 03:53 PM
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
07-09-2016, 04:00 PM
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
07-09-2016, 04:13 PM
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
07-09-2016, 04:24 PM
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
07-09-2016, 04:36 PM
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
07-09-2016, 04:55 PM
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
07-09-2016, 05:04 PM
IT WORKS GREAT THANK YOU!!!!