CODEWITHSUNDEEP
arraysexcelvba
jblack
jblack

Reputation: 576

Excel VBA - Separating elements of a 1D array by fixed width

Suppose I have data in range A1:A100. I would like to split each cell in the range to multiple columns, by a fixed width, eg (0-10,10-15,15-37). I could use the Text-to-Columns function in both vba and excel itself.

My question is, if i pass the range to an array first in VBA:

Dim my Array as Variant
myArray = Range("A1:A100").value

How would i apply the following logic:

myNewArray = Array(myArray(0,10),myArray(10,15),myArray(15,37))

or maybe like this:

for i=1 to 100
    myNewArray(i,1) = mid(myArray(i),0,10)
    myNewArray(i,2) = mid(myArray(i),10,5)    
    myNewArray(i,3) = mid(myArray(i),15,22)
next

which would result in a new array of 100 rows by 3 columns, but having split the initial data at the specified points, like how a Text-to-Column approach would. But these approaches don't seem to work.

I have tried searching for answer to this but can't seem to find anything

Any help would be appreciated, thanks

Upvotes: 2

Views: 405

Answers (1)

T.M.
T.M.

Reputation: 9948

In addition to Scott 's correct hint in comment you could use one datafield array only (based on three columns) and do a reverse loop splitting the first "column":

Option Explicit

Sub Split_them()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet") ' << change to your sheet name
Dim i As Long, j As Long
Dim v
Dim a(): a = Array(0, 11, 16, 38)   ' Fixed Widths (first item zero)
v = ws.Range("A1:C100")
For i = 1 To UBound(v, 1)
    For j = 3 To 1 Step -1
        v(i, j) = Mid(v(i, 1), a(j - 1) + 1, a(j) - a(j - 1))
    Next j
Next i
' write back to sheet
ws.Range("A1:C100").Offset(0, 2) = v
End Sub

Upvotes: 2

Related Questions