YouTube, Video making and editing, etc. coupled to excelfox ( windows Movie Maker )

**DocAElstein** · 12-31-2018, 12:43 AM

Code:

'

Sub Populatenumbersfromrangeofnumbers2() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31531&p=244015#p244015
Dim Ws1 As Worksheet: Set Ws1 = Worksheets("Sheet1")
Dim arrSN() As Variant: Let arrSN() = Ws1.Range("F2:F" & Ws1.Range("F" & Rows.Count & "").End(xlUp).Row & "").Value ' range of current SNs
Dim arrGrpsOut() As String: ReDim arrGrpsOut(1 To 1) ' 1 Dimensional  Array for output values.
Dim cnt As Long, Cnt2 As Long, Rng2 As Long, Rng1 As Long, Rws As Long
    For cnt = LBound(arrSN(), 1) To UBound(arrSN(), 1)
    Dim SpltRng() As String: Let SpltRng() = Split(arrSN(cnt, 1), " - ", 2, vbBinaryCompare)
    Dim arrRws() As Variant 'Array for 1 2 3 4 5 6 7 etc
     Let arrRws() = Evaluate("=row(" & SpltRng(0) & ":" & SpltRng(1) & ")") ' This returns a one "column" 2 Dimensional array of all the numbers between the ranges
     Let Rng1 = UBound(arrGrpsOut()) + 1 ' The start of a range must be just above the last one
     Let Rng2 = Rng1 + SpltRng(1) - SpltRng(0) ' 'this gives the top of the current range in indicies of arrGrpsOut()
     ReDim Preserve arrGrpsOut(1 To Rng2)
        For Cnt2 = Rng1 To Rng2
         Let arrGrpsOut(Cnt2) = arrRws(Cnt2 - Rng1 + 1, 1) ' Cnt2 is the indicie in arrGrpsOut(), for the indicie in arrRws() we need to start at 1, then 2 3 4 5
        Next Cnt2
    Next cnt

Dim arrOut() As String: ReDim arrOut(1 To UBound(arrGrpsOut()) - 1, 1 To 1) ' a 2 dimension, 1 column , to be easy to post the results of this array into a column
    For cnt = 1 To UBound(arrGrpsOut()) - 1
     Let arrOut(cnt, 1) = arrGrpsOut(cnt + 1)
    Next cnt

 Let Ws1.Range("K2").Resize(UBound(arrOut(), 1), 1) = arrOut()
End Sub
Sub Populatenumbersfromrangeofnumbers2_2() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31531&p=244015#p244015
Dim Ws1 As Worksheet: Set Ws1 = Worksheets("Sheet1")
Dim arrSN() As Variant: Let arrSN() = Ws1.Range("G2:G" & Ws1.Range("G" & Rows.Count & "").End(xlUp).Row & "").Value ' range of current SNs
Dim arrGrpsOut() As String: ReDim arrGrpsOut(1 To 1) ' 1 Dimensional  Array for output values.
Dim cnt As Long, Cnt2 As Long, Rng2 As Long, Rng1 As Long, Rws As Long
    For cnt = LBound(arrSN(), 1) To UBound(arrSN(), 1)
    Dim SpltRng() As String: Let SpltRng() = Split(arrSN(cnt, 1), " - ", 2, vbBinaryCompare)
    Dim arrRws() As Variant 'Array for 1 2 3 4 5 6 7 etc
     Let arrRws() = Evaluate("=row(" & SpltRng(0) & ":" & SpltRng(1) & ")") ' This returns a one "column" 2 Dimensional array of all the numbers between the ranges
     Let Rng1 = UBound(arrGrpsOut()) + 1 ' The start of a range must be just above the last one
     Let Rng2 = Rng1 + SpltRng(1) - SpltRng(0) ' 'this gives the top of the current range in indicies of arrGrpsOut()
     ReDim Preserve arrGrpsOut(1 To Rng2)
        For Cnt2 = Rng1 To Rng2
         Let arrGrpsOut(Cnt2) = arrRws(Cnt2 - Rng1 + 1, 1) ' Cnt2 is the indicie in arrGrpsOut(), for the indicie in arrRws() we need to start at 1, then 2 3 4 5
        Next Cnt2
    Next cnt

Dim arrOut() As String: ReDim arrOut(1 To UBound(arrGrpsOut()) - 1, 1 To 1) ' a 2 dimension, 1 column , to be easy to post the results of this array into a column
    For cnt = 1 To UBound(arrGrpsOut()) - 1
     Let arrOut(cnt, 1) = arrGrpsOut(cnt + 1)
    Next cnt

 Let Ws1.Range("L2").Resize(UBound(arrOut(), 1), 1) = arrOut()
End Sub

_____ Workbook: Sample.xlsm ( Using Excel 2007 32 bit )

SN		Some expected result	Number inside Group
1 - 244	1600 - 1843		244	1	1600
245 - 448	700 - 903		204	2	1601
449 - 750	398 - 699		302	3	1602
751 - 1003	1844 - 2096		253	4	1603
1004 - 1266	1144 - 1406		263	5	1604
1267 - 1489	2097 - 2319	1 - 223	223	6	1605
1490 - 1698	189 - 397		209	7	1606
1699 - 1938	904 - 1143	224 - 463	240	8	1607
1939 - 2126	1 - 188		188	9	1608
2127 - 2319	1407 - 1599		193	10	1609
			2319	11	1610
				12	1611
				13	1612
				14	1613
				15	1614
				16	1615
				17	1616
				18	1617
				19	1618
				20	1619
				21	1620
				22	1621
				23	1622
				24	1623
				25	1624
				26	1625
				27	1626
				28	1627
				29	1628
				30	1629
				31	1630
				32	1631
				33	1632
				34	1633