SelectionChange VBA Code Help, conditionals and time values OR use comma separated string of integers to select cells in a column

[fhqwgads]

tldr: I need code that, on selection of any cell in F8:F27, will add cells corresponding to the numbers from the third position of the comma separated string where cell E10 is the first number and E29 is the 20th number, so selecting F8 will add E10, E11, E12, E14, E15, E21 and E27 to the active selection.

Using Excel 2016

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
7
8			20	40, 12:01:53, 1, 2, 3, 5, 6, 12, 18		20	40, 10:47:36, 1, 2, 3, 5, 6, 12, 18		20	40, 13:07:56, 1, 2, 3, 5, 6, 12, 18		20	40, 13:13:31, 1, 2, 3, 5, 6, 12, 18			20	40, 14:13:39, 1, 2, 3, 5, 6, 12, 18
9			40	40, 12:01:53, 1, 2, 3, 6, 8, 13, 17		40	40, 10:47:36, 1, 2, 3, 6, 8, 13, 17		40	40, 13:07:56, 1, 2, 3, 6, 8, 13, 17		40	40, 13:13:31, 1, 2, 3, 6, 8, 13, 17			40	40, 14:13:39, 1, 2, 3, 6, 8, 13, 17
10		1	8.44	40, 12:01:53, 1, 2, 3, 7, 13		8.44	40, 10:47:36, 1, 2, 3, 7, 13		8.44	40, 13:07:56, 1, 2, 3, 7, 13		8.44	40, 13:13:31, 1, 2, 3, 7, 13			8.44	40, 14:13:39, 1, 2, 3, 7, 13
11		2	5.46	40, 12:01:53, 1, 2, 4, 5, 6, 8, 12, 17		5.46	40, 10:47:36, 1, 2, 4, 5, 6, 8, 12, 17		5.46	40, 13:07:56, 1, 2, 4, 5, 6, 8, 12, 17		5.46	40, 13:13:31, 1, 2, 4, 5, 6, 8, 12, 17			5.46	40, 14:13:39, 1, 2, 4, 5, 6, 8, 12, 17
12		3	9.65	40, 12:01:53, 1, 2, 4, 5, 7, 12		9.65	40, 10:47:36, 1, 2, 4, 5, 7, 12		9.65	40, 13:07:56, 1, 2, 4, 5, 7, 12		9.65	40, 13:13:31, 1, 2, 4, 5, 7, 12			9.65	40, 14:13:39, 1, 2, 4, 5, 7, 12
13		4	9.32	40, 12:01:53, 1, 2, 5, 6, 7, 8, 11, 14, 16		9.32	40, 10:47:36, 1, 2, 5, 6, 7, 8, 11, 14, 16		9.32	40, 13:07:56, 1, 2, 5, 6, 7, 8, 11, 14, 16		9.32	40, 13:13:31, 1, 2, 5, 6, 7, 8, 11, 14, 16			9.32	40, 14:13:39, 1, 2, 5, 6, 7, 8, 11, 14, 16
14		5	0.07	40, 12:01:53, 1, 2, 5, 6, 8, 12, 13, 20		0.07	40, 10:47:36, 1, 2, 5, 6, 8, 12, 13, 20		0.07	40, 13:07:56, 1, 2, 5, 6, 8, 12, 13, 20		0.07	40, 13:13:31, 1, 2, 5, 6, 8, 12, 13, 20			0.07	40, 14:13:39, 1, 2, 5, 6, 8, 12, 13, 20
15		6	4.57	40, 12:01:53, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19		4.57	40, 10:47:36, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19		4.57	40, 13:07:56, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19		4.57	40, 13:13:31, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19			4.57	40, 14:13:39, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19
16		7	7.48	40, 12:01:53, 1, 2, 5, 7, 8, 9, 11, 16, 20		7.48	40, 10:47:36, 1, 2, 5, 7, 8, 9, 11, 16, 20		7.48	40, 13:07:56, 1, 2, 5, 7, 8, 9, 11, 16, 20		7.48	40, 13:13:31, 1, 2, 5, 7, 8, 9, 11, 16, 20			7.48	40, 14:13:39, 1, 2, 5, 7, 8, 9, 11, 16, 20
17		8	2.03	40, 12:01:53, 1, 2, 5, 7, 10, 17, 19, 20		2.03	40, 10:47:36, 1, 2, 5, 7, 10, 17, 19, 20		2.03	40, 13:07:56, 1, 2, 5, 7, 10, 17, 19, 20		2.03	40, 13:13:31, 1, 2, 5, 7, 10, 17, 19, 20			2.03	40, 14:13:39, 1, 2, 5, 7, 10, 17, 19, 20
18		9	8.25	40, 12:01:53, 1, 2, 5, 7, 13, 16, 18, 19		8.25	40, 10:47:36, 1, 2, 5, 7, 13, 16, 18, 19		8.25	40, 13:07:56, 1, 2, 5, 7, 13, 16, 18, 19		8.25	40, 13:13:31, 1, 2, 5, 7, 13, 16, 18, 19			8.25	40, 14:13:39, 1, 2, 5, 7, 13, 16, 18, 19
19		10	9.46	40, 12:01:53, 1, 2, 8, 9, 15, 16, 17, 19		9.46	40, 10:47:36, 1, 2, 8, 9, 15, 16, 17, 19		9.46	40, 13:07:56, 1, 2, 8, 9, 15, 16, 17, 19		9.46	40, 13:13:31, 1, 2, 8, 9, 15, 16, 17, 19			9.46	40, 14:13:39, 1, 2, 8, 9, 15, 16, 17, 19
20		11	7.02	40, 12:01:54, 1, 2, 8, 11, 15, 16, 17, 19, 20		7.02	40, 10:47:36, 1, 2, 8, 11, 15, 16, 17, 19, 20		7.02	40, 13:07:56, 1, 2, 8, 11, 15, 16, 17, 19, 20		7.02	40, 13:13:31, 1, 2, 8, 11, 15, 16, 17, 19, 20			7.02	40, 14:13:39, 1, 2, 8, 11, 15, 16, 17, 19, 20
21		12	9.23	40, 12:01:54, 1, 2, 9, 10, 14, 16, 17, 18		9.23	40, 10:47:36, 1, 2, 9, 10, 14, 16, 17, 18		9.23	40, 13:07:56, 1, 2, 9, 10, 14, 16, 17, 18		9.23	40, 13:13:31, 1, 2, 9, 10, 14, 16, 17, 18			9.23	40, 14:13:39, 1, 2, 9, 10, 14, 16, 17, 18
22		13	8.97	40, 12:01:54, 1, 2, 10, 11, 14, 16, 17, 18, 20		8.97	40, 10:47:36, 1, 2, 10, 11, 14, 16, 17, 18, 20		8.97	40, 13:07:56, 1, 2, 10, 11, 14, 16, 17, 18, 20		8.97	40, 13:13:31, 1, 2, 10, 11, 14, 16, 17, 18, 20			8.97	40, 14:13:39, 1, 2, 10, 11, 14, 16, 17, 18, 20
23		14	4.91	40, 12:01:54, 1, 2, 11, 12, 13, 17		4.91	40, 10:47:36, 1, 2, 11, 12, 13, 17		4.91	40, 13:07:56, 1, 2, 11, 12, 13, 17		4.91	40, 13:13:31, 1, 2, 11, 12, 13, 17			4.91	40, 14:13:39, 1, 2, 11, 12, 13, 17
24		15	7.94	40, 12:01:54, 1, 3, 5, 8, 11, 14, 16, 17, 19		7.94	40, 10:47:36, 1, 3, 5, 8, 11, 14, 16, 17, 19		7.94	40, 13:07:56, 1, 3, 5, 8, 11, 14, 16, 17, 19		7.94	40, 13:13:31, 1, 3, 5, 8, 11, 14, 16, 17, 19			7.94	40, 14:13:39, 1, 3, 5, 8, 11, 14, 16, 17, 19
25		16	0.02	40, 12:01:54, 1, 3, 5, 13, 14, 15, 16		0.02	40, 10:47:36, 1, 3, 5, 13, 14, 15, 16		0.02	40, 13:07:56, 1, 3, 5, 13, 14, 15, 16		0.02	40, 13:13:31, 1, 3, 5, 13, 14, 15, 16			0.02	40, 14:13:39, 1, 3, 5, 13, 14, 15, 16
26		17	0.88	40, 12:01:54, 1, 3, 6, 9, 17, 19, 20		0.88	40, 10:47:36, 1, 3, 6, 9, 17, 19, 20		0.88	40, 13:07:56, 1, 3, 6, 9, 17, 19, 20		0.88	40, 13:13:31, 1, 3, 6, 9, 17, 19, 20			0.88	40, 14:13:39, 1, 3, 6, 9, 17, 19, 20
27		18	2.58	40, 12:01:54, 1, 3, 6, 10, 16, 17, 19		2.58	40, 10:47:36, 1, 3, 6, 10, 16, 17, 19		2.58	40, 13:07:56, 1, 3, 6, 10, 16, 17, 19		2.58	40, 13:13:31, 1, 3, 6, 10, 16, 17, 19			2.58	40, 14:13:39, 1, 3, 6, 10, 16, 17, 19
28		19	6.98	12:01:54		6.98	10:47:36		6.98	13:07:56		6.98	13:13:31			6.98	14:13:39
29		20	1.23	12:01:53		1.23	10:47:36		1.23	13:07:56		1.23	13:13:31			1.23	14:13:39
30
Sheet2

long version: i tried to make some code to work in conjunction with Tushar Mehta's code that finds all sets of amounts that sum to a given value
https://www.mrexcel.com/forum/excel...tion-numbers-equal-given-sum.html#post1445541
http://www.tushar-mehta.com/excel/templates/match_values/index.html
basically their code works like this. if you have a column of numbers, add two more cells above your list, the first being the number of desired combinations that add to your given sum with 0 being all combinations, and the second number being the sum you want combinations of. when you select the cells starting with the number of desired combinations number and ending with the bottom of your list and run the code, in the next column to the right it gives a list of comma separated strings with the strings starting with the sum, the time this combination was found and a list of values corresponding to the order of numbers in your list. then at the bottom it gives you the end and start time that the code was calculating

in my example i made of list of 20 randomly generated numbers and asked it to find 20 combinations that add to 40. in column D i added some helper numbers to show the corresponding order of numbers in the list. all other cells i the sheet are blank

what i want my code to do is when a cell with a list is selected, for excel to add the corresponding cells to the active selection

so far i have this code in a workbook module
i left some notes in the code to explain my thought process but basically i was trying to find the start of the list of numbers to sum so that the comma separated numbers know where to start referencing

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


If 1 = 0 Then

Else

If IsError(ActiveCell.Value) = True Then

Else

Dim i As Long
Dim i2 As Long
Dim a As Long
Dim Arr As Variant
Dim Arr2 As Variant
Dim lNumElements As Long
Dim lNumElements2 As Long
Dim starttime As Double
Dim endtime As Double
Dim liststart As String
Dim MySel As Range
Dim cellold As Range

'Step 1: determining if cell is a valid comma separated list created by TM's code
'       by creating 1 dimensional array split by commas and checking if the number
'       of array elements is greater than 1 which would ignore any cells with no commas
'       and check if the second value in the array has two colons to show it is a time
'Step 2: find the cells at the end of the list that have the start and end times


Arr = Split(ActiveCell.Value, ",")
lNumElements = UBound(Arr) - LBound(Arr) + 1
If lNumElements > 1 Then


    If Len(Arr(1)) - Len(Replace(Arr(1), ":", "")) = 2 Then
    
        For i = 1 To 2000
        
            If TimeValue(Arr(1)) <= ActiveCell.Offset(i, 0).Value And Len(ActiveCell.Offset(i, 0).Value) - Len(Replace(ActiveCell.Offset(i, 0).Value, ",", "")) = 0 Then
                If ActiveCell.Offset(i, 0).Value <= ActiveCell.Offset(i + 1, 0).Value Then
                    
                    Exit For
                Else
                    
                    Exit For
                End If
            End If
        Next i
        
    End If
    
Else

End If


If i = 0 Then

Else
'MsgBox i
a = i
Arr2 = Split(ActiveCell.Offset(i - 1, 0).Value, ",")
lNumElements2 = UBound(Arr2) - LBound(Arr2) + 1
endtime = ActiveCell.Offset(a, 0).Value
starttime = ActiveCell.Offset(a + 1, 0).Value
'MsgBox endtime

'Step 3: find the top of the list by looping up through the column and checking if the
'       time value in the second array position is between the start and end times,
'       and then find the first cell in the range of numbers to sum by offsetting

For i = a To (-ActiveCell.Row + 1) Step -1
    Arr2 = Split(ActiveCell.Offset(i - 1, 0).Value, ",")
    lNumElements2 = UBound(Arr2) - LBound(Arr2) + 1

    If lNumElements2 <= 1 Then
        liststart = ActiveCell.Offset(i + 2, -1).Address
        Exit For
    Else
        If (TimeValue(Arr2(1)) <= endtime And TimeValue(Arr2(1)) >= starttime) = True Then
            Else
            liststart = ActiveCell.Offset(i + 2, -1).Address
            Exit For
        End If
    End If

Next i
'MsgBox liststart
If lNumElements > 2 And (Arr(2)) = "" Then

Else

i = 2

Set MySel = ActiveCell
Set cellold = ActiveCell

'Step 4: create range using values in array and select

For i = 2 To UBound(Arr)
    Set MySel = Union(MySel, Range(liststart).Offset(Arr(i) - 1, 0))
Next i
MySel.Select
cellold.Activate

'MsgBox Format(endtime, "hh:mm:ss")
'MsgBox Format(starttime, "hh:mm:ss")
End If

End If

End If

End If

End Sub

when used on the sample above it only works at selecting the correct cells on columns F, L and S but not on I or O. the only difference is the time stamps of when i ran the code so i figured there must be a problem in my code when referring to time values but i have no idea what it is exactly.

if there's any other code out there that can find combinations of numbers to add to a given value better than MT then i guess that would work too

ps. sorry if my code is an awful mess to look at, i'm still a novice with VBA coding and excel. this is also my first time using arrays in VBA since i couldn't think of a way to do without. i think some of my IF statements are redundant or could be combined or something, like that one bit where i have IF... THEN Exit For ELSE Exit For, but i'm too scared to change it since it's kinda working and i'm pretty sure the problem is elsewhere. sometimes i look at code i've made and can't tell what i was trying to do half the time

 

[fhqwgads]

shameful self bump