vba 使用宏向列数据添加标题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10640453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add headers to column data using a macro
提问by Robert Wilson
I'm in need of a simple macro that adds the column header values to the contents in the columns of a spreadsheet (preferably values that are specified).
我需要一个简单的宏，将列标题值添加到电子表格列中的内容（最好是指定的值）。
So if possible, I'd like to specify the column names in VBA (Col1="Location") so that the macro is only applied to specific columns.
因此，如果可能，我想在 VBA (Col1="Location") 中指定列名，以便宏仅应用于特定列。
Example: If I've specified, "Location" as a column header the macro should look for and A1 has "Location" as the header, then everything in A needs, "Location: " added to the front of it. Basically, whatever the header is + ": ".
示例：如果我已指定“Location”作为宏应查找的列标题，而 A1 将“Location”作为标题，则 A 中的所有内容都需要将“Location:”添加到它的前面。基本上，无论标题是+“：”。
So this:
所以这：
Location
A04B25
A05B89
B58C23
Would be this:
会是这样：
Location
Location: A04B25
Location: A05B89
Location: B58C23
This macro would need to cycle through each column and add that column header value to the values in the column IFit's on the list.
这个宏需要循环遍历每一列，并将该列标题值添加到列中的值，如果它在列表中。
This is the code that I'm trying to use that isn't working:
这是我尝试使用但不起作用的代码：
Sub AppendHeader()
Dim i, LastCol
LastCol = Range("IV1").End(xlToLeft).Column
For i = 1 To LastCol
If UCase(Cells(1, i).Value) = "Local SKU" Then
Cells(1, i).EntireColumn.Append = UCase(Cells(1, i).Value) + ": "
End If
If UCase(Cells(1, i).Value) = "Supplier's SKU" Then
Cells(1, i).EntireColumn.Append = UCase(Cells(1, i).Value) + ": "
End If
Next
End Sub
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim preString As String
Dim lastRow As Long, LastCol As Long, i As Long, j As Long
Set ws = Sheets("Sheet1")
With ws
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LastCol
Select Case Trim(UCase(Cells(1, i).Value))
Case "LOCAL SKU", "SUPPLIER'S SKU"
lastRow = .Range(Split(Cells(, i).Address, "$")(1) & Rows.Count).End(xlUp).Row
preString = .Cells(1, i).Value & ": "
For j = 2 To lastRow
.Cells(j, i).Value = preString & .Cells(j, i).Value
Next j
End Select
Next i
End With
End Sub
回答by Zairja
There is a similar problemon SO, but I have come up with a different VBA solution. It will change the Number Format of the columns (except for the header row) based on that column's header.
SO上有一个类似的问题，但我想出了一个不同的VBA解决方案。它将根据该列的标题更改列的数字格式（标题行除外）。
To do this manually, you could select the "Custom" category for Format Cells and enter
要手动执行此操作，您可以为单元格格式选择“自定义”类别并输入
"Location: "General;"Location: "@
This will make "Location: " show up in front of numbers, text, dates and such. Any formulas applied to these cells will take into account the prefix (Location:) but suppose you wanted to work with just the values. With this method, you can easily remove the formatting rather than creating a second subroutine to remove the prefix.
这将使“位置：”显示在数字、文本、日期等之前。应用于这些单元格的任何公式都将考虑前缀 ( Location:)，但假设您只想使用这些值。使用此方法，您可以轻松删除格式，而不是创建第二个子例程来删除前缀。
The code modifies Siddharth's -- Thank you, sir -- (I have not explicitly declared all the variables as he has, but that is best practice).
代码修改了 Siddharth 的 -- 谢谢，先生 -- （我没有像他那样明确声明所有变量，但这是最佳实践）。
Sub Sample2()
Set ws = Sheets("Sheet1")
With ws
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LastCol
lastRow = .Range(Split(Cells(, i).Address, "$")(1) & Rows.Count).End(xlUp).Row
preString = .Cells(1, i).Value & ": "
Range(Cells(2, i), Cells(lastRow, i)).NumberFormat = _
Chr(34) & preString & Chr(34) & "General;" & _
Chr(34) & preString & Chr(34) & "@"
Next i
End With
End Sub
