Split function - assign to array
 

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

Split function - assign to array
 

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
---------------
Jacob Skaria


"PBcorn" wrote:

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

Split function - assign to array
 

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

"PBcorn" wrote in message
...
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