Add column # dynamically in row 1 of a particular worksheet

[Guest]

I'm trying to use row 1 of a particular worksheet to include the column # in
each and every column of the worksheet. As I add/insert a new column anywear
on the worksheet, it should automatically re-adjust the column numbers
without having to copy or resequence the column numbers (i.e., every single
column should have a formula "=columns()"). Is this possible using VBA code?
If so, can someone please post the code. Thanks in advance for your help.

Roger

 

[Guest]

you can put the formula in using VBA. try something like this:

ActiveSheet.Range("A1").Formula = "=COLUMN()"

 

[Guest]

Thank you, but if I want to insert a column, can I get it to automatically
add the formula without having to run a macro? I'm trying to get it to add
the formula dynamically. Let's say I have the formula in all 256 columns in
row 1. When I insert a new column (using the standard insert column menu) in
the sheet, I want it to automatically add the formula, keeping the columns in
sequence.

 

[Guest]

Conversely, if I were to delete a few columns, it would re-sequence the
columns and still include the formula in all 256 columns on row 1.

 

[Guest]

Try this code in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.EntireColumn.Cells.Count = Target.Cells.Count And
Target.Columns.Count = 1 Then
'if this returns true, an entire column was changed, possibly inserted
'persist the formula in the first cell
Target.Cells(1).Formula = "=COLUMN()"
End If

End Sub

 

[Guest]

That looks like it might be it but I'm getting an error on the If...Then
segment of the code. It is displayed in red on the module.

 

[Guest]

probably because it wrapped to the next line.. the "If" up to the "Then"
should be all in one line...

 

[Guest]

Thank you so much Vergel! That is exactly what I needed. Hope you have an
awesome day!

Roger