Split function - assign to array

[PBcorn]

I am using the split function to return the arguments in sum functions, of
which there are many in each worksheet. Eg a cell with =sum(a1,a2,a3,a4), I
need to return a1 to a4 then read these into a dynamic array. Split function
returns a 1-d array but i can't work out how to read into inparray(). Help
appreciated. Thanks

Dim c As Range
Dim sht As Worksheet
Dim inparray() As String
Dim sformula As String
Dim i As Integer
Dim l As Integer


For Each sht In ActiveWorkbook.Worksheets

For Each c In sht.UsedRange.Cells

If c.HasFormula = True Then
l = Len(c.Formula) - 6
sformula = Mid(c.Formula, 6, l)
ReDim inparray(UBound(Split(sformula, ",", -1))) As String
inparray() = Split(sformula, ",", -1)
Else: End If


Next c

Next sht

End Sub

 

[Jacob Skaria]

Try the below macro...The string variable is split to an array variable...The
lower bound of the array would be 0..

Sub Macro()
Dim strData As String, arrData As Variant

strData = "a,b,c,d,e"
arrData = Split(strData, ",")

For intTemp = 0 To UBound(arrData)
MsgBox arrData(intTemp)
Next

End Sub

If this post helps click Yes

 

[Peter T]

Sub test()
Dim i As Long
Dim sFml As String
Dim arrArgs() As String

sFml = "=sum(a1,a2,a3,a4)"

If Left$(UCase(sFml), 5) = "=SUM(" Then
If InStr(6, sFml, ",") Then
arrArgs = Split(Mid$(sFml, 6, Len(sFml) - 6), ",")
For i = 0 To UBound(arrArgs)
Debug.Print arrArgs(i)
Next
End If
End If
End Sub

Regards,
Peter T