Before I reinvent the wheel is anyone aware of some publicly available vba that will parse a range input string like "3-7" and return a list of numbers like "3,4,5,6,7". Or a little more advanced like "1,3-5,12" returns "1,3,4,5,12"
Before I reinvent the wheel is anyone aware of some publicly available vba that will parse a range input string like "3-7" and return a list of numbers like "3,4,5,6,7". Or a little more advanced like "1,3-5,12" returns "1,3,4,5,12"
Return the numbers where?
I expect this will be original code for your requirement. It will involve common structures like loops and conditionals, maybe even an array.
One solution based on the two data samples provided:
Code:Function GetSeq(strIN As String) As String Dim aryS As Variant, strOut As String, x As Integer, y As Integer aryS = Split(strIN, ",") For x = 0 To UBound(aryS) If IsNumeric(aryS(x)) Then strOut = strOut & aryS(x) & "," ElseIf InStr(aryS(x), "-") > 0 Then For y = Split(aryS(x), "-")(0) To Split(aryS(x), "-")(1) strOut = strOut & y & "," Next End If Next GetSeq = strOut End Function
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
@June - modified your code slightly to remove the trailing comma, hope you don't mind
Code:Function GetSeq(strIN As String) As String Dim aryS As Variant, strOut As String, x As Integer, y As Integer aryS = Split(strIN, ",") For x = 0 To UBound(aryS) If IsNumeric(aryS(x)) Then strOut = strOut & "," & aryS(x) ElseIf InStr(aryS(x), "-") > 0 Then For y = Split(aryS(x), "-")(0) To Split(aryS(x), "-")(1) strOut = strOut & "," & y Next End If Next GetSeq = Mid(strOut, 2) End Function
@June7, nice work!
I was thinking of leaving it as is except for dealing with the trailing comma as
strOut = Left(strOut, InStrRev(strOut, ",") - 1)
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Yes, told myself not to forget that then guess what - forgot it. Thanks.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks June!