PDA

View Full Version : [SOLVED] Need help getting results of macro displayed, starting at row 5 column B thru F



estatefinds
08-11-2019, 09:22 AM
I need the results after selecting a combination to be displayed at the top under thin line on the worksheet(ie: starting at row 5 column B thru F).
so I select a combination and the Macro will display the row each number of the combination was found below the combanation that was selected , then I select another combination and the previous results will clear and the results of the selected combination will display.


Currently when I run macro everytime I go down to the next combination the results go down the page, I dont want that to happen I need results to stay at the top.


The row result should line up with the row labels i had placed in column I5 where there are row numbers 1 to 71.

please let me know if any questions.

Any help on this is appreciated!! Thank you






Private Sub Worksheet_SelectionChange(ByVal Target As Range) 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

Bob Phillips
08-12-2019, 03:29 AM
Give an example of a multi-row selection, and what you would expect to see as a result.

estatefinds
08-12-2019, 03:54 AM
Select the first combination in the work sheet and the result will appear as it should. this is how it should work on all of them.

I want the other subsequent combinations to result starting at row 5 column B to F. It’s an event code where I select a combination one at a time and the result will always be in the area starting at row 5 columns B through F. the first combination that is selected and the result to the right is how it should look.

so if i decide to freeze pane below this area and select a combination i can still see the results each time a select a combination.

Bob Phillips
08-12-2019, 05:21 AM
Okay, let's try again - you may understand what you want, but you need to convey that to us.

What should it look like if I select A6? What should it look like if I select A7 and A8?

estatefinds
08-12-2019, 07:17 AM
ok when the worksheet is opened , you ll see the combinations in column A starting at row 4. when i select by brining down the cursor onto the combination 9-10-18-27-32 the result is to the right in columns starting at row 5 B through F. the number 1 that is found on column C row 5 shows that the number 9 was found one space below in combination 2-9-17-19-26 and found as the second number in combination so therfore the one in the colums C row 5. and so on with the remaing four numbers in bolumn B through F.

now the 1 the 4 the 9 the 15 and the 23 in the columns B though F notice are alligned on the row that is matching the row in the column I . which is listed 1 to 71. first one is Done.
now when i select the next combination with cursor 2-9-17-19-26 the result appears to the right which is the 3 the 2 the 26 the 4 the 14 .


so the 3 in column B corresonds to the the 2 in the combination;

the 2 in coulmn C corresponds to the number 17 in the combination,

the 26 in column D corresponds to the 9 in the combination ,

4 in column E corresponds to the number 19,


14 in column E corresponds to the number 26 in the combination.


so these numbers will appear in theeir correct columns but now need them to be on the same level of rows.
3 2 26 4 14 will be on the same level as the rows in column I labeled.

so 3 will stay in its column B but be located on row B7 to be on the same labeled row I3.


the 2 will be in column C but on C6 which will then be on the same level as the labeled row 2 on I 2.
etc.

let me know if you under stand.

Bob Phillips
08-12-2019, 10:02 AM
Thank-you, it took some reading but (I think) I understand it now.

This works, but one effect that I am not sure that you want is that some previous entries get cleared. For example, if you select A4 then A5, you will get a 3 in B7, as well as 4 others scattered around. So far so good. Then select A6, and the 3 in B7 gets cleared out, as does the 6, 4, and the 14 (the 2 doesn't as it is in the row above A6). This may be what you want, it doesn't seem right to me, but then again, this whole thing confuses me :dunno

Anyway, this is my shot.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SearchRange As Range
Dim writeCell As Range, oneCell As Range
Dim Numerals As Variant, i As Long

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

Numerals = Split(CStr(Target.Value), "-")
With Target

Set SearchRange = Range(.Cells(2, 1), Me.Cells(Rows.Count, .Column).End(xlUp))
End With
SearchRange.Offset(0, 1).Resize(, 6).ClearContents

For i = 0 To UBound(Numerals)

Set writeCell = Nothing
For Each oneCell In SearchRange.Cells

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

Debug.Print Numerals(i), writeCell.Row - Target.Row
With writeCell

Cells(4 + .Row - Target.Row, Application.Match(Numerals(i), Split(.Value, "-"), 0) + 1).Value = .Row - Target.Row
'.Offset(0, Application.Match(Numerals(i), Split(.Value, "-"), 0)).Value = .Row - Target.Row
End With
End If
Next i
End Sub

estatefinds
08-12-2019, 11:08 AM
everything combination should behave like the first combination.
so when the combination A4 the results will show five vales only the 9 23 1 4 15 on the B through F columns. these are correct.

now when the A5 is selected the results should be the 3 on B7 , 2 on C6, 26 on d 26 , the 4 on E8, 14 on E14. there should be no other values showing. so wheni select A4 then A5 the values 1 on C5 stays there, that should not happen. there should be only 5 values with each selection in the A column at a time.

ok so looking at this if I select A6 which is the combination

3-8-15-29-35

the results should be 3 found 2 spaced down there fore the value 2 be placed in column C6 to be allinged with the column I value of 2.

now the value 8 found 3 spaces down therefore 3 be placed in column D7 to be alligned with the column I value of 2.

now the 15 found 10 spaces down therefore the value 10 placed in D14 to be alligned with column I value of 10.

now the 29 found 11 spaces down therefor the value 11 placed in F15 to be alligned with the column I value of 11.

now the number 35 is found 20 spaces down therefore the value 20 gets placed in the F23 allinged to the value in column I of 20.


also you ll noticed where ever the number was found in the combinations below whether it was the first, second, third , fourth digit, and fifth;

so since the value of the combination 3 was found below the selected combination 2 spaces and was found in the second spot of the combination it got placed in the column C.


B C D E F
1 2 3 4 5

Bob Phillips
08-12-2019, 12:38 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SearchRange As Range
Dim writeCell As Range, oneCell As Range
Dim Numerals As Variant
Dim lastrow As Long, i As Long

With Me

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

If Target.Row < 4 Or Target.Row > lastrow Or Target.Column <> 1 Or Target.Cells.Count > 1 Then Beep: Exit Sub

Numerals = Split(CStr(Target.Value), "-")
With Target

Set SearchRange = .Cells(2, 1).Resize(lastrow - .Row)
End With
.Range("B5").Resize(lastrow - 4, 5).ClearContents

For i = 0 To UBound(Numerals)

Set writeCell = Nothing
For Each oneCell In SearchRange.Cells

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

Cells(4 + .Row - Target.Row, Application.Match(Numerals(i), Split(.Value, "-"), 0) + 1).Value = .Row - Target.Row
End With
End If
Next i
End With
End Sub

estatefinds
08-12-2019, 01:09 PM
im testing now! thank you!! ill get back to you soon! to let you know if any issues. thank you

estatefinds
08-12-2019, 04:40 PM
I ran it and tested the macro and it works just as it needs to!!!!!:clap::clap::clap::clap::clap::clap::clap::clap::clap::clap: Great Job!!!!! thank you very much!!!!