Updating Text to column Fieldinfo:= using loop

[pranav_d]

I am trying to pull array info from FYI sheet so that I can change breaks as needed and then do text to column on Data tab. But it is not working getting Run time error. Can you help me how to get break points from one sheet and then apply text to column in second sheet with data?

Sub txtclm()

Sheets("FYI").Select

a = "Array(Array(0, 2),"

c = Cells(2, 1).End(xlDown).Row

For i = 2 To c

If i <> c Then
a = a & " Array(" & Cells(i, 2) & ", 2),"
Else
a = a & " Array(" & Cells(i, 2) & ", 2))"
End If
Next i

Sheets("Data").Select

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=a
End Sub

 

[BSALV]

can you give 5 lines of your data
this is almost correct, but i can't test it.
in AA1:AB9 you have the numbers, you want to use

Sub txtclm()
     With Sheets("FYI")
          a = Application.Transpose(Evaluate("=" & .Range("AA1:AB9").Address))
          .Range("A1:A10").TextToColumns Destination:=.Range("b1"), DataType:=xlFixedWidth, FieldInfo:=a
     End With
End Sub

Map4
	Z	AA	AB	AC
1		0	2
2		3	2
3		8	2
4		14	2
5		22	2
6		30	2
7		38	2
8		44	2
9		51	2
10
Blad4

 

[pranav_d]

I tried but it is not working. Lets suppose first tab "Data" has values as aaaa, bbbb, cccc, dddd and break points are in second tab from cell B2 are 1, 2, 3, 4 etc., so I want macro to separate a in each column a a a a. You can see below in macro screenshot that Arrays are getting created by loop but Fieldinfo:= is not accepting it.

 

[BSALV]

try this with an empty sheet

Sub Pranav()

     iNumber = 26

     Dim FI()                                                   'declare FieldInfo-array
     ReDim FI(1, iNumber - 1)                                   'redim(1 less then then number of characters)
     For i = 0 To UBound(FI, 2)                                 'loop
          FI(0, i) = i                                          '1st form 0 to inumber-1(=position of the width
          FI(1, i) = 1                                          'general
     Next

     With Range("B1:B180")
     'preparation of the data to separate
          .Offset(, -1).FormulaR1C1 = "=REPT(CHAR(ROW()+64)," & iNumber + 5 & ")"     'so many characters as iNumber + 5 extra
          .Value = .Offset(, -1).Value                          'the value of that formula

msgbox "ready ?"     
'real stuff
          Application.DisplayAlerts = False
          .TextToColumns Destination:=.Cells(1, 2), DataType:=xlFixedWidth, FieldInfo:=Application.Transpose(FI)     ' 'separate the B-column to the C-column and following columns, 1 character width
          Application.DisplayAlerts = True

     End With
End Sub

 

[pranav_d]

Thanks a lot, this was verify helpful. I learnt a few new things because of it.