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

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

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


--
Hope that helps.

Vergel Adriano


"Roger L" wrote:

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

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.

"Vergel Adriano" wrote:

> you can put the formula in using VBA. try something like this:
>
> ActiveSheet.Range("A1").Formula = "=COLUMN()"
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Roger L" wrote:
>
> > 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
> >

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.

"Roger L" wrote:

> 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.
>
> "Vergel Adriano" wrote:
>
> > you can put the formula in using VBA. try something like this:
> >
> > ActiveSheet.Range("A1").Formula = "=COLUMN()"
> >
> >
> > --
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "Roger L" wrote:
> >
> > > 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
> > >

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



--
Hope that helps.

Vergel Adriano


"Roger L" wrote:

> 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.
>
> "Vergel Adriano" wrote:
>
> > you can put the formula in using VBA. try something like this:
> >
> > ActiveSheet.Range("A1").Formula = "=COLUMN()"
> >
> >
> > --
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "Roger L" wrote:
> >
> > > 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
> > >

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.

"Vergel Adriano" wrote:

> 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
>
>
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Roger L" wrote:
>
> > 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.
> >
> > "Vergel Adriano" wrote:
> >
> > > you can put the formula in using VBA. try something like this:
> > >
> > > ActiveSheet.Range("A1").Formula = "=COLUMN()"
> > >
> > >
> > > --
> > > Hope that helps.
> > >
> > > Vergel Adriano
> > >
> > >
> > > "Roger L" wrote:
> > >
> > > > 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
> > > >

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

--
Hope that helps.

Vergel Adriano


"Roger L" wrote:

> 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.
>
> "Vergel Adriano" wrote:
>
> > 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
> >
> >
> >
> > --
> > Hope that helps.
> >
> > Vergel Adriano
> >
> >
> > "Roger L" wrote:
> >
> > > 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.
> > >
> > > "Vergel Adriano" wrote:
> > >
> > > > you can put the formula in using VBA. try something like this:
> > > >
> > > > ActiveSheet.Range("A1").Formula = "=COLUMN()"
> > > >
> > > >
> > > > --
> > > > Hope that helps.
> > > >
> > > > Vergel Adriano
> > > >
> > > >
> > > > "Roger L" wrote:
> > > >
> > > > > 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
> > > > >

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

Roger


"Vergel Adriano" wrote:

> probably because it wrapped to the next line.. the "If" up to the "Then"
> should be all in one line...
>
> --
> Hope that helps.
>
> Vergel Adriano
>
>
> "Roger L" wrote:
>
> > 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.
> >
> > "Vergel Adriano" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > --
> > > Hope that helps.
> > >
> > > Vergel Adriano
> > >
> > >
> > > "Roger L" wrote:
> > >
> > > > 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.
> > > >
> > > > "Vergel Adriano" wrote:
> > > >
> > > > > you can put the formula in using VBA. try something like this:
> > > > >
> > > > > ActiveSheet.Range("A1").Formula = "=COLUMN()"
> > > > >
> > > > >
> > > > > --
> > > > > Hope that helps.
> > > > >
> > > > > Vergel Adriano
> > > > >
> > > > >
> > > > > "Roger L" wrote:
> > > > >
> > > > > > 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
> > > > > >