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

Question

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

the second approach will work if you refer to myArray correctly. that array even though it is referring to a single column is a 2D array. So use myArray(i,1) — Scott Craner
– Scott Craner, Commented May 18, 2018 at 17:42
also Mid starts at 1 not 0 and there are only 9 in the first unless you want the 10 character used twice. — Scott Craner
– Scott Craner, Commented May 18, 2018 at 17:46
@ScottCraner Thanks for the suggestion, edited as suggested, seems to give an error still, invalid procedure call, not sure what would be causing that — jblack
– jblack, Commented May 18, 2018 at 20:55

T.M. · Accepted Answer · 2018-05-18 18:33:01Z

2

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

edited May 18, 2018 at 18:33

answered May 18, 2018 at 18:00

T.M.

10k3 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jblack Over a year ago

Thanks a lot! Works perfectly, very concise code...will adapt it to my data, thanks again

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related