Fill ROW with alpha (a-z)

[Guest]

My wife works tech support at a large company. Her specialty is Outlook, but
she covers all MS Office products to a degree. Her job description precludes
her from supporting VBA programming, so guess who gets delegated to smoke out
the answer? Here's her question:

"I need to copy the alphabet across and this formula will only copy the
alphabet down.

=LEFT(ADDRESS(1,ROW(B1),4,1),FIND("1",ADDRESS(1,ROW(B1),4,1))-1)"

However looking at the formula she sent me, it appears to have nothing to do
with the question. Sheesh!

Anyway, copying the alphabet across is what she mentioned to me before, and
I suspect that is really what she wants. (Be nice to me -- Anita is wife
v.2.0, as of less than two years, and the "period of adjustment" is still in
full flower <g>!)

For something so simple, and assuming it would be needed to use on various
WSs, This dorky macro does it:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/4/2007 by Administrator
'

'
ActiveCell.FormulaR1C1 = "A"
Range("B1").Select
ActiveCell.FormulaR1C1 = "B"
Range("C1").Select
ActiveCell.FormulaR1C1 = "C"
Range("D1").Select
..
..
..
ActiveCell.FormulaR1C1 = "Y"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Z"
Range("A1").Select
End Sub

But surely there must be a formula that could be fired off in a single cell
to do it?

Could it be that this clown's formula is trying to capture the letters from
the column names? I can't make head nor tail of it.

 

[Guest]

=LEFT(ADDRESS(1,COLUMN(A1),4,1),FIND("1",ADDRESS(1,COLUMN(A1),4,1))-1)


"Dave Birley" skrev:

 

[Guest]

A lot simpler then copying down. Try this

=CHAR(64+COLUMN())

It works if you are starting in column A where column() is 1. If you are
starting elswhere adjust the 64 as necessary to make the sum of the 2 = 65
which is ASCII capital A.

Any good?

Mike

 

[Guest]

Incidentally I forgot to mention the formula works for fill down also if you
substitute ROW for COLUMN and its a lot simpler than the one below.

Mike

 

[Scoops]

My wife works tech support at a large company. Her specialty is Outlook, but
she covers all MS Office products to a degree. Her job description precludes
her from supporting VBA programming, so guess who gets delegated to smoke out
the answer? Here's her question:

"I need to copy the alphabet across and this formula will only copy the
alphabet down.

=LEFT(ADDRESS(1,ROW(B1),4,1),FIND("1",ADDRESS(1,ROW(B1),4,1))-1)"

However looking at the formula she sent me, it appears to have nothing to do
with the question. Sheesh!

Anyway, copying the alphabet across is what she mentioned to me before, and
I suspect that is really what she wants. (Be nice to me -- Anita is wife
v.2.0, as of less than two years, and the "period of adjustment" is still in
full flower <g>!)

For something so simple, and assuming it would be needed to use on various
WSs, This dorky macro does it:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/4/2007 by Administrator
'

'
ActiveCell.FormulaR1C1 = "A"
Range("B1").Select
ActiveCell.FormulaR1C1 = "B"
Range("C1").Select
ActiveCell.FormulaR1C1 = "C"
Range("D1").Select
.
.
.
ActiveCell.FormulaR1C1 = "Y"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Z"
Range("A1").Select
End Sub

But surely there must be a formula that could be fired off in a single cell
to do it?

Could it be that this clown's formula is trying to capture the letters from
the column names? I can't make head nor tail of it.

You could type the alphabet in once and then:

Highlight the range
Tools > Options > Custom Lists tab (the "Import list from cells" box
should contain the higlighted range address)
Click Import

Now you can type an alpha anywhere in a sheet and use the fill handle
to complete the alphabet.

Regards

Steve

 

[Guest]

Ooooooooooooooooh -- COLUMN, yeah, yeah. Excellent would be true! (However I
like Mike's one even better - geekier <g>!

Thanks a million.

 

[Guest]

Deliciously geeky. I passed both yours and "excellent"'s along, so they can
stop bugging my wife now -- and she can stop bugging me <g>!

Can't thank you enough,

 

[Guest]

Wow, with all these neat answers, it almost makes me wish it were *my*
problem to be solved. I shall pass yours along with the rest to wifey <g>!
Thanks!!!

 

[Guest]

If you prefer to use VBA then Horizontally:

Sub Alphabet()
For i = 65 To 90
Cells(1, i - 64) = Chr(i)
Next
End Sub

Or vertically:

Sub Alphabet()
For i = 65 To 90
Cells(i - 64, 1) = Chr(i)
Next
End Sub

 

[Guest]

Delicious -- this will drive my wife nuts <g>! Thanks.