使用宏将标题添加到列数据 [英] Add headers to column data using a macro

问题描述

我需要一个简单的宏，将列标题值添加到电子表格的列中（最好是指定的值）。

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).

所以如果可能，我想在VBA（Col1 =Location）中指定列名，以便宏仅适用于特定列。

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.

示例：
如果我指定了位置作为宏应该查找的列标题，并且A1具有位置作为标题，则A中的所有内容都需要添加到其前面。
基本上，不管标题是：。

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
A04B25
A05B89
B58C23

将是这样的：

Location
Location: A04B25
Location: A05B89
Location: B58C23

此宏将需要循环遍历每个列，并将该列标题值添加到列表中的 IF 列。

This macro would need to cycle through each column and add that column header value to the values in the column IF it'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

推荐答案

这是你正在尝试的吗？

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

这篇关于使用宏将标题添加到列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！