Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign function return value to an array | Excel Programming | |||
Assign Values to array | Excel Discussion (Misc queries) | |||
Passing result of multiple split function results to 1 Array | Excel Programming | |||
Assign formula array in vba | Excel Programming | |||
How do I assign values to an array? | Excel Programming |