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!!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.