Is there a way in Excel VBA to have a two characters functions in creating an Array-CodePudding

I currently have a VBA code that makes an array based on "/" between values of a string.

IDnum = Range(Target.Address).Value
IDArray() = Split(IDnum, "/")

I'm trying to edit it the code to make it take "-" or ">" (not necessarily both just need one of them) and do the range on either numbers on either side as well as doing "/" function I already have in the array. IE: input is "101-105/501-503" the array would be {101 102 103 104 105 501 502 503}. I keep trying to think of ways to do this and feel I need to do a loop with an if statement. Am I on the right thinking or is there a simple line of code that can make it happen.

Edit my overall code is:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
Dim KeyCells As Range, KeyCells2 As Range, R1 As Range
Set R1 = Range("C6:H319")
Set KeyCells = R1
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Dim IDnum As String, IDArray() As String, Counter As Integer, CIDArray() As Integer, strg As Integer
    Dim i As Integer, Watt As Double, TWatt As Double, TAmp As Double
    TWatt = 0
    'Split apart different fixtures
    IDnum = Range(Target.Address).Value
    IDArray() = Split(IDnum, "/")
    'Apply indexmatch to IDArray
    For Counter = LBound(IDArray) To UBound(IDArray)
        i = (IDArray(Counter) Mod 1000) - (IDArray(Counter) Mod 100)
        Watt = Application.WorksheetFunction.Index(Sheets("Fixture List").Range("L3:L10"), Application.WorksheetFunction.Match(i, Sheets("Fixture List").Range("A3:A10"), 0), 1)
        TWatt = TWatt   Watt
    Next Counter
Set KeyCells2 = Target.Cells
KeyCells2.Offset(1, 12).Value = TWatt
End If
End Sub

CodePudding user response：

You can create a temporary array consisting of your hyphenates, then loop from the start to the end of the hyphenates, create a string of those numbers and finally split them all into one array.

    Dim mystr As String
    Dim splitarr As Variant
    Dim temparr As Variant
    Dim tempstr As String
    
    mystr = "101-105/501-503"
    splitarr = Split(mystr, "/")
    
    Dim i As Long
    Dim j As Long
    
    For i = 0 To UBound(splitarr)
        temparr = Split(splitarr(i), "-")
        For j = CLng(temparr(0)) To CLng(temparr(1))
            If Not tempstr = "" Then 'This keeps from having an empty string as index 0.
                tempstr = tempstr & "/" & j
            Else
                tempstr = j
            End If
        Next j
    Next i
            
    splitarr = Split(tempstr, "/")
            
    For i = 0 To UBound(splitarr)
        Debug.Print splitarr(i)
    Next i

I don't know what you are doing with your array so I'm just dumping the results to the immediate window.

CodePudding user response：

In addition to @Warcupine 's valid solution, I demonstrate an approach without a second loop by building a numeric sequence via help function nums() ... // a) vers. MS 365, b) prior.

Example Call

Sub ExampleCall()
'0. define string input
    Dim mystr As String
    mystr = "101-105/501-503"
'1. split into main parts (delimited by "/")
    Dim splitArr As Variant
    splitArr = Split(mystr, "/")
'2. get arrays of enumerated sequences via function nums()
    Dim i As Long
    For i = 0 To UBound(splitArr)
        Dim temparr  As Variant
        temparr = Split(splitArr(i), "-")
        splitArr(i) = nums(CLng(temparr(0)), CLng(temparr(1)))  ' << help function nums()
    Next i
'3. split joined main parts (by unique delimiter "/")
    splitArr = Split(Join(splitArr, "/"), "/")
'4. display in VB editor's immediate window
    Debug.Print Join(splitArr, ",")  '~~> 101,102,103,104,105,501,502,503
End Sub

Help function nums()

The help function returns numeric sequences joined together by a "/" delimiter.

a) Using version MS 365 via Sequence() function:

    Function nums(ByVal n As Long, ByVal nn As Long) As String
        nums = Join(Application.Sequence(1, nn - n   1, n, 1), "/")
    End Function

Syntax: =SEQUENCE(rows,[columns],[start],[step])

b) Prior versions (other than MS 365):

It's possible to create a numeric sequence by evaluation of column ranges.

Caveat: numeric inputs have an upper limit of 16,384 (upper columns boundary)

    Function nums(ByVal n As Long, ByVal nn As Long) As String
    nums= Split(Cells(, n).Address, "$")(1) & ":" & _
           Split(Cells(, nn).Address, "$")(1)
    nums= Join(Evaluate("column(" & nums & ")"), "/")
End Function