How can I rewrite this using an Array?
 

I have the below block of code that adds a simple header row:
-------
Public Sub add_headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1:BD1").Select
Selection.NumberFormat = "@"

Count = 0

For Each cell In Selection
Count = Count + 1
cell.Value = "D" & Count
Next cell

End Sub
-------

How can I rewrite this using an Array to indicate my range instead of
selecting it? The Array would have 56 elements.

Thanks
Fred

How can I rewrite this using an Array?
 

Public Sub add_headers()

Rows("1:1").Insert
Range("A1:BD1").Selection.NumberFormat = "@"

Count = 0

For Each cell In Range("A1:BD1")
Count = Count + 1
cell.Value = "D" & Count
Next cell

End Sub


--
HTH

Bob Phillips

"Fred" wrote in message
oups.com...
I have the below block of code that adds a simple header row:
-------
Public Sub add_headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1:BD1").Select
Selection.NumberFormat = "@"

Count = 0

For Each cell In Selection
Count = Count + 1
cell.Value = "D" & Count
Next cell

End Sub
-------

How can I rewrite this using an Array to indicate my range instead of
selecting it? The Array would have 56 elements.

Thanks
Fred

How can I rewrite this using an Array?
 

Another possibility:

Sub AddHeaders()
Rows(1).Insert
Range("A1:BD1").Formula = "=""D""& Column()"
Range("A1:BD1").Formula = Range("A1:BD1").Value

End Sub

--
Regards,
Tom Ogilvy


"Fred" wrote in message
oups.com...
I have the below block of code that adds a simple header row:
-------
Public Sub add_headers()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1:BD1").Select
Selection.NumberFormat = "@"

Count = 0

For Each cell In Selection
Count = Count + 1
cell.Value = "D" & Count
Next cell

End Sub
-------

How can I rewrite this using an Array to indicate my range instead of
selecting it? The Array would have 56 elements.

Thanks
Fred

How can I rewrite this using an Array?
 

Thanks Tom, much cleaner...

But what if I did not want to specify my ending column. In this case I
needed 56 columns so I counted over manually to find the 56th column.
But what if I wanted the 213th column.

I was thinking an Array for this: Range("A1:BD1").

How can I rewrite this using an Array?
 

Sub AddHeaders()
ncol = 56
Rows(1).Insert
Range("A1").Resize(1,ncol).Formula = "=""D""& Column()"
Range("A1").Resize(1,ncol).Formula = _
Range("A1").Resize(1,ncol).Value
End Sub

I don't see where adding an array into the equation would be of any
advantage.

--
Regards,
Tom Ogilvy

"Fred" wrote in message
oups.com...
Thanks Tom, much cleaner...

But what if I did not want to specify my ending column. In this case I
needed 56 columns so I counted over manually to find the 56th column.
But what if I wanted the 213th column.

I was thinking an Array for this: Range("A1:BD1").

How can I rewrite this using an Array?
 

Thanks Tom,

That's the trick knowing when to use what. Your code even got rid of my
For Next loop.

Now that I know 'Resize" exists I was able to rewrite with it, although
your code is cleaner.

Public Sub add_headers()

Rows(1).Insert
ncol = 56
Set workrange = Range("A1").Resize(1, ncol).Resize
Count = 0

For Each cell In workrange
Count = Count + 1
cell.Value = "D" & Count
Next cell

Rows("1:1").Font.Bold = True

End Sub

How can I rewrite this using an Array?
 

Thanks Bob, I never thought to use the actual range ref in the For Next
loop. That's the problem with using the macro recorder and then
modifying the code, it is real easy to have messy bloated code.

Fred

How can I rewrite this using an Array?
 

Since the cells are sequential is there some way to use FillRight?

"Tom Ogilvy" wrote:

Sub AddHeaders()
ncol = 56
Rows(1).Insert
Range("A1").Resize(1,ncol).Formula = "=""D""& Column()"
Range("A1").Resize(1,ncol).Formula = _
Range("A1").Resize(1,ncol).Value
End Sub

I don't see where adding an array into the equation would be of any
advantage.

--
Regards,
Tom Ogilvy

"Fred" wrote in message
oups.com...
Thanks Tom, much cleaner...

But what if I did not want to specify my ending column. In this case I
needed 56 columns so I counted over manually to find the 56th column.
But what if I wanted the 213th column.

I was thinking an Array for this: Range("A1:BD1").