使用VBA将文本转换为Excel中的列 [英] Convert text to columns in Excel using VBA

问题描述

我正在尝试使用宏将文本转换为列，但是我无法做到这一点，我已经尝试记录一个宏来实现这一点，但是由于文本到列VBA，我遇到了一些问题功能预计会有选择，有没有办法我可以选择让我们说范围A7：A50000？或更好的A7：lastnonempty单元格？

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?

谢谢，

FYI，分米不重要，因为我需要这样做将文本转换为公式

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

这是我的代码

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

推荐答案

尝试这个

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

请注意。（例如 .Range ），它们指的是使用语句对象

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

这篇关于使用VBA将文本转换为Excel中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！