使用VBA将文本转换为Excel中的列

I'm trying to convert text to columns using a macro but I'm not able to do it, I have tried to record a macro to achieve this, however I'm running into some issues since the text to columns VBA function expects a selection, is there a way I can dinamically chose let's say range A7:A50000? or even better A7:lastnonempty cell?

我正在尝试使用宏将文本转换为列,但我无法做到这一点,我试图记录一个宏来实现这一点,但是我遇到了一些问题,因为文本到列VBA函数需要一个选择,有没有一种方法我可以用dinamically选择让我们说范围A7:A50000?甚至更好的A7:lastnonempty细胞?

Thanks,

FYI, the delimeter is not important since I need to do this to convert text into formula

仅供参考,分隔符并不重要,因为我需要这样做才能将文本转换为公式

Here is my code

这是我的代码

Range("O6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("O6"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

2 个解决方案

#1

Try this

Sub Txt2Col()
    Dim rng As Range

    Set rng = [C7]
    Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))

    rng.TextToColumns Destination:=rng, DataType:=xlDelimited, ' rest of your settings

Update: button click event to act on another sheet

更新:按钮单击事件以在另一个工作表上操作

Private Sub CommandButton1_Click()
    Dim rng As Range
    Dim sh As Worksheet

    Set sh = Worksheets("Sheet2")
    With sh
        Set rng = .[C7]
        Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))

        rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote,  _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, 
        Space:=False, 
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    End With
End Sub

Note the .'s (eg .Range) they refer to the With statement object

注意。的(例如.Range)它们引用With语句对象

#2

If someone is facing issue using texttocolumns function in UFT. Please try using below function.

如果有人在UFT中使用texttocolumns函数面临问题。请尝试使用以下功能。

myxl.Workbooks.Open myexcel.xls
myxl.Application.Visible = false `enter code here`
set mysheet = myxl.ActiveWorkbook.Worksheets(1)
Set objRange = myxl.Range("A1").EntireColumn
Set objRange2 = mysheet.Range("A1")
objRange.TextToColumns objRange2,1,1, , , , true

Here we are using coma(,) as delimiter.

这里我们使用昏迷(,)作为分隔符。

#1