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

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
216
Office Version
  1. 2019
Platform
  1. Windows
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
CDEFGHIJKLMNOPQRST
7
82040, 12:01:53, 1, 2, 3, 5, 6, 12, 182040, 10:47:36, 1, 2, 3, 5, 6, 12, 182040, 13:07:56, 1, 2, 3, 5, 6, 12, 182040, 13:13:31, 1, 2, 3, 5, 6, 12, 182040, 14:13:39, 1, 2, 3, 5, 6, 12, 18
94040, 12:01:53, 1, 2, 3, 6, 8, 13, 174040, 10:47:36, 1, 2, 3, 6, 8, 13, 174040, 13:07:56, 1, 2, 3, 6, 8, 13, 174040, 13:13:31, 1, 2, 3, 6, 8, 13, 174040, 14:13:39, 1, 2, 3, 6, 8, 13, 17
1018.4440, 12:01:53, 1, 2, 3, 7, 138.4440, 10:47:36, 1, 2, 3, 7, 138.4440, 13:07:56, 1, 2, 3, 7, 138.4440, 13:13:31, 1, 2, 3, 7, 138.4440, 14:13:39, 1, 2, 3, 7, 13
1125.4640, 12:01:53, 1, 2, 4, 5, 6, 8, 12, 175.4640, 10:47:36, 1, 2, 4, 5, 6, 8, 12, 175.4640, 13:07:56, 1, 2, 4, 5, 6, 8, 12, 175.4640, 13:13:31, 1, 2, 4, 5, 6, 8, 12, 175.4640, 14:13:39, 1, 2, 4, 5, 6, 8, 12, 17
1239.6540, 12:01:53, 1, 2, 4, 5, 7, 129.6540, 10:47:36, 1, 2, 4, 5, 7, 129.6540, 13:07:56, 1, 2, 4, 5, 7, 129.6540, 13:13:31, 1, 2, 4, 5, 7, 129.6540, 14:13:39, 1, 2, 4, 5, 7, 12
1349.3240, 12:01:53, 1, 2, 5, 6, 7, 8, 11, 14, 169.3240, 10:47:36, 1, 2, 5, 6, 7, 8, 11, 14, 169.3240, 13:07:56, 1, 2, 5, 6, 7, 8, 11, 14, 169.3240, 13:13:31, 1, 2, 5, 6, 7, 8, 11, 14, 169.3240, 14:13:39, 1, 2, 5, 6, 7, 8, 11, 14, 16
1450.0740, 12:01:53, 1, 2, 5, 6, 8, 12, 13, 200.0740, 10:47:36, 1, 2, 5, 6, 8, 12, 13, 200.0740, 13:07:56, 1, 2, 5, 6, 8, 12, 13, 200.0740, 13:13:31, 1, 2, 5, 6, 8, 12, 13, 200.0740, 14:13:39, 1, 2, 5, 6, 8, 12, 13, 20
1564.5740, 12:01:53, 1, 2, 5, 6, 8, 13, 16, 17, 18, 194.5740, 10:47:36, 1, 2, 5, 6, 8, 13, 16, 17, 18, 194.5740, 13:07:56, 1, 2, 5, 6, 8, 13, 16, 17, 18, 194.5740, 13:13:31, 1, 2, 5, 6, 8, 13, 16, 17, 18, 194.5740, 14:13:39, 1, 2, 5, 6, 8, 13, 16, 17, 18, 19
1677.4840, 12:01:53, 1, 2, 5, 7, 8, 9, 11, 16, 207.4840, 10:47:36, 1, 2, 5, 7, 8, 9, 11, 16, 207.4840, 13:07:56, 1, 2, 5, 7, 8, 9, 11, 16, 207.4840, 13:13:31, 1, 2, 5, 7, 8, 9, 11, 16, 207.4840, 14:13:39, 1, 2, 5, 7, 8, 9, 11, 16, 20
1782.0340, 12:01:53, 1, 2, 5, 7, 10, 17, 19, 202.0340, 10:47:36, 1, 2, 5, 7, 10, 17, 19, 202.0340, 13:07:56, 1, 2, 5, 7, 10, 17, 19, 202.0340, 13:13:31, 1, 2, 5, 7, 10, 17, 19, 202.0340, 14:13:39, 1, 2, 5, 7, 10, 17, 19, 20
1898.2540, 12:01:53, 1, 2, 5, 7, 13, 16, 18, 198.2540, 10:47:36, 1, 2, 5, 7, 13, 16, 18, 198.2540, 13:07:56, 1, 2, 5, 7, 13, 16, 18, 198.2540, 13:13:31, 1, 2, 5, 7, 13, 16, 18, 198.2540, 14:13:39, 1, 2, 5, 7, 13, 16, 18, 19
19109.4640, 12:01:53, 1, 2, 8, 9, 15, 16, 17, 199.4640, 10:47:36, 1, 2, 8, 9, 15, 16, 17, 199.4640, 13:07:56, 1, 2, 8, 9, 15, 16, 17, 199.4640, 13:13:31, 1, 2, 8, 9, 15, 16, 17, 199.4640, 14:13:39, 1, 2, 8, 9, 15, 16, 17, 19
20117.0240, 12:01:54, 1, 2, 8, 11, 15, 16, 17, 19, 207.0240, 10:47:36, 1, 2, 8, 11, 15, 16, 17, 19, 207.0240, 13:07:56, 1, 2, 8, 11, 15, 16, 17, 19, 207.0240, 13:13:31, 1, 2, 8, 11, 15, 16, 17, 19, 207.0240, 14:13:39, 1, 2, 8, 11, 15, 16, 17, 19, 20
21129.2340, 12:01:54, 1, 2, 9, 10, 14, 16, 17, 189.2340, 10:47:36, 1, 2, 9, 10, 14, 16, 17, 189.2340, 13:07:56, 1, 2, 9, 10, 14, 16, 17, 189.2340, 13:13:31, 1, 2, 9, 10, 14, 16, 17, 189.2340, 14:13:39, 1, 2, 9, 10, 14, 16, 17, 18
22138.9740, 12:01:54, 1, 2, 10, 11, 14, 16, 17, 18, 208.9740, 10:47:36, 1, 2, 10, 11, 14, 16, 17, 18, 208.9740, 13:07:56, 1, 2, 10, 11, 14, 16, 17, 18, 208.9740, 13:13:31, 1, 2, 10, 11, 14, 16, 17, 18, 208.9740, 14:13:39, 1, 2, 10, 11, 14, 16, 17, 18, 20
23144.9140, 12:01:54, 1, 2, 11, 12, 13, 174.9140, 10:47:36, 1, 2, 11, 12, 13, 174.9140, 13:07:56, 1, 2, 11, 12, 13, 174.9140, 13:13:31, 1, 2, 11, 12, 13, 174.9140, 14:13:39, 1, 2, 11, 12, 13, 17
24157.9440, 12:01:54, 1, 3, 5, 8, 11, 14, 16, 17, 197.9440, 10:47:36, 1, 3, 5, 8, 11, 14, 16, 17, 197.9440, 13:07:56, 1, 3, 5, 8, 11, 14, 16, 17, 197.9440, 13:13:31, 1, 3, 5, 8, 11, 14, 16, 17, 197.9440, 14:13:39, 1, 3, 5, 8, 11, 14, 16, 17, 19
25160.0240, 12:01:54, 1, 3, 5, 13, 14, 15, 160.0240, 10:47:36, 1, 3, 5, 13, 14, 15, 160.0240, 13:07:56, 1, 3, 5, 13, 14, 15, 160.0240, 13:13:31, 1, 3, 5, 13, 14, 15, 160.0240, 14:13:39, 1, 3, 5, 13, 14, 15, 16
26170.8840, 12:01:54, 1, 3, 6, 9, 17, 19, 200.8840, 10:47:36, 1, 3, 6, 9, 17, 19, 200.8840, 13:07:56, 1, 3, 6, 9, 17, 19, 200.8840, 13:13:31, 1, 3, 6, 9, 17, 19, 200.8840, 14:13:39, 1, 3, 6, 9, 17, 19, 20
27182.5840, 12:01:54, 1, 3, 6, 10, 16, 17, 192.5840, 10:47:36, 1, 3, 6, 10, 16, 17, 192.5840, 13:07:56, 1, 3, 6, 10, 16, 17, 192.5840, 13:13:31, 1, 3, 6, 10, 16, 17, 192.5840, 14:13:39, 1, 3, 6, 10, 16, 17, 19
28196.9812:01:546.9810:47:366.9813:07:566.9813:13:316.9814:13:39
29201.2312:01:531.2310:47:361.2313:07:561.2313:13:311.2314: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

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top