文本到列VBA目标到新工作表 [英] Text To Columns VBA Destination onto new sheet

问题描述

我对Excel VBA文本到列有问题.显然，该功能固定在我希望将目标放置在新图纸上的情况下，将目标放置在同一图纸上.

I'm having a problem with Excel VBA Text To Columns. Apparently the function is fixated on putting the destination on the same sheet when I want it to be on a new sheet.

Worksheets("RawData").Columns(ID).TextToColumns Destination:=Worksheets("Regular").Columns("C"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=True, _
    OtherChar:="(", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

显然，这只会将其转储到原始数据的C列中，而不是我想要的常规"工作表中.

Apparently this will just dump it on Column C of raw data rather than the worksheet of "Regular" where i want it to be.

推荐答案

另一种方法是直接使用字符串操作.假设您希望冒号成为您在text to column命令中的定界符:

Another approach would be using string manipulation directly. Assuming you want the colon to be your delimiter in the text to column command:

dim i as integer
dim j as integer
dim strTemp as string
dim arrString() as string

for i = 1 to 'number of rows in your first sheet
    strTemp = cells(i, 1)
    arrStrings = strings.split(strTemp, ",")
    for j = 1 to ubound(arrStrings)
        sheet2.cells(i, j) = arrStrings(j)
    next j
next i

这篇关于文本到列VBA目标到新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！