数据中的锯齿阵列|文本到列 [英] Jagged Arrays in Data | Text To Columns
问题描述
我有什么
让我们举个例子说明这个代码。
Sub Sample()
Dim ws As Worksheet
设置ws = ThisWorkbook.Sheets(Sheet1)
ws.Columns(1).TextToColumns _
Destination:= Range(A1),_
DataType:= xlFixedWidth,_
FieldInfo:= Array(_
Array (0,1,1),阵列(60,1),阵列(120,1),阵列(180,1),_
阵列(240,1),阵列(300,1) 1),Array(420,1)_
),_
TrailingMinusNumbers:= True
End Sub
我想要的
在一小组数据中,上述代码可以工作。但是如果我想要说出 Array(2700,1)
,该怎么办?这意味着我必须写它 46
times Array(0,1),Array(60,1)... Array(1080,1 ).... Array(2700,1)
我尝试了
我尝试使用这种方法
Sub Sample()
Dim ws As Worksheet
Dim MyArray
Dim MyStr As String
Dim i As Long
For i = 0 To 2700 Step 60
MyStr = MyStr& #& Array(& i&,1)
Next i
MyStr = Mid(MyStr,2)
MyArray = Split(MyStr, #)
设置ws = ThisWorkbook.Sheets(Sheet1)
ws.Columns(1).TextToColumns _
目标:=范围( A1),_
DataType:= xlFixedWidth,_
FieldInfo:= MyArray,_
TrailingMinusNumbers:= True
End Sub
显然,这将不起作用 Array(i,1)
以字符串形式存储 MyArray
。
我的问题
有没有什么办法可以在一个循环中创建这样一个锯齿状的数组,所以我要做的就是说
FieldInfo:= MyArray
您可以将数组分配给数组项,如下所示:
Dim n As Long
n = 0
ReDim MyArray(2700 \ 60)
对于i = 0至2700步骤60
MyArray(n)=数组(i,1)
n = n + 1
Next i
What I have
Let's take an example of this code which works.
Sub Sample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Columns(1).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array( _
Array(0, 1), Array(60, 1), Array(120, 1), Array(180, 1), _
Array(240, 1), Array(300, 1), Array(360, 1), Array(420, 1) _
), _
TrailingMinusNumbers:=True
End Sub
What I want
In a small set of data, the above code works. But what if I want to go up to say Array(2700,1)
? This means I will have to write it 46
times Array(0, 1), Array(60, 1)...Array(1080, 1)....Array(2700, 1)
What I have tried
I tried using this approach
Sub Sample()
Dim ws As Worksheet
Dim MyArray
Dim MyStr As String
Dim i As Long
For i = 0 To 2700 Step 60
MyStr = MyStr & "#" & "Array(" & i & ",1)"
Next i
MyStr = Mid(MyStr, 2)
MyArray = Split(MyStr, "#")
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Columns(1).TextToColumns _
Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=MyArray, _
TrailingMinusNumbers:=True
End Sub
Obviously that won't work as Array(i,1)
is stored as a string in MyArray
.
My Question
Is there any way we can create such kind of jagged arrays in a loop so that all I have to do is say FieldInfo:=MyArray
You can assign an array to an array item like so:
Dim n As Long
n = 0
ReDim MyArray(2700 \ 60)
For i = 0 To 2700 Step 60
MyArray(n) = Array(i, 1)
n = n + 1
Next i
这篇关于数据中的锯齿阵列|文本到列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!