How can I rewrite this using an Array?

[Fred]

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

 

[Bob Phillips]

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

 

[Tom Ogilvy]

Another possibility:

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

End Sub

 

[Fred]

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

 

[Fred]

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

 

[Guest]

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