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

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)

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)
End If
End If
End Sub

Peter T

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question