Solved How do I change column headers in excel

November 27, 2016 at 06:43:33
Specs: Windows 10
I have about 5 columns that I need to change the column headers and would like to do so using a macro. So far I have
Sub namefixer()
s = Array("PALLET ID", "CATEGORY", "SUB-CATEGORY", "CONDITION", "RETAIL", "EXT RETAIL")
Range("F1:M1") = s

But it is only changes the columns to the first 3 categories that I have listed.

The columns I need to change are F,G,H,L,M


See More: How do I change column headers in excel

Report •

#1
November 27, 2016 at 17:40:50
✔ Best Answer
When I run your code I get this:

          F         G         H          I       J        K        L   M
  1  PALLET ID CATEGORY SUB-CATEGORY CONDITION RETAIL EXT RETAIL #N/A #N/A


That makes sense because you told the code to write the Array to F1:M1. VBA has no way of knowing that you want to skip certain columns. How could it? Therefore it fills contiguous cells starting at F1 and then can't find values for L1:M1. Therefore it returns #N/A.

There is something else about your requirements that doesn't make sense. You loaded the Array with 6 values, but you say "The columns I need to change are F,G,H,L,M"

That's only 5 columns.

In any case, you code should look something like this, although I don't really know what you want to do with the 6 values. I'll put them in F1:H1 and K1:M1 just to illustrate how to "skip" columns.

Sub namefixer_V1()
 s = Array("PALLET ID", "CATEGORY", "SUB-CATEGORY")
 t = Array("CONDITION", "RETAIL", "EXT RETAIL")
  Range("F1:H1") = s
  Range("K1:M1") = t
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •
Related Solutions


Ask Question