时间戳

首页 > 解决方案 > 将文本隐藏到列并插入带有列名的标题

excel - 将文本隐藏到列并插入带有列名的标题

问题描述

下面是我可以将文本转换为列的代码。

我在找什么？我想用 Tno、Host、Data、String、ID、Jno 添加标题（向下移动第一行并添加标题）。你能帮忙吗？

Sub test()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
    Case "MASTER", "DATA"
        'do nothing
    Case Else
        ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, OtherChar:="|", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
            TrailingMinusNumbers:=True
End Select
Next ws
Application.ScreenUpdating = True
End Sub

标签： excelexcel-formulavba

解决方案

for each在循环之后添加以下代码：

Sub test()
Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
    Case "MASTER", "DATA"
        'do nothing
    Case Else
        ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, OtherChar:="|", _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), _
            TrailingMinusNumbers:=True
End Select
Next ws

    'insert header
    Range("A1").EntireRow.Insert
    Range("A1").Value = "Tno"
    Range("B1").Value = "Host"
    Range("C1").Value = "Data"
    Range("D1").Value = "String"
    Range("E1").Value = "ID"
    Range("F1").Value = "Jno"

Application.ScreenUpdating = True
End Sub

推荐阅读