Add headers to column data using a macro

Question

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.

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 + ": ".

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

Robert, if you do not mind, can you edit the above question and post the code there as the comment box doesn't format the code properly. — Siddharth Rout
– Siddharth Rout, Commented May 17, 2012 at 18:41

Siddharth Rout · Accepted Answer · 2012-05-17 18:59:37Z

2

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

answered May 17, 2012 at 18:59

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zairja Over a year ago

The OP is probably long gone, but I had a similar problem and found this answer, so +1. I came up with my own method, as well. Thanks!

Community · Accepted Answer · 2017-05-23 12:31:40Z

There is a similar problem on 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.

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.

The code modifies Siddharth's -- Thank you, sir -- (I have not explicitly declared all the variables as he has, but that is best practice).

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

Collectives™ on Stack Overflow

Add headers to column data using a macro

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related