Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
=LEFT(ADDRESS(1,COLUMN(A1),4,1),FIND("1",ADDRESS(1 ,COLUMN(A1),4,1))-1)
"Dave Birley" skrev: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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 "Dave Birley" wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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 "Dave Birley" wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
On 4 May, 13:27, Dave Birley
wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
Ooooooooooooooooh -- COLUMN, yeah, yeah. Excellent would be true! (However I
like Mike's one even better - geekier <g! Thanks a million. -- Dave Temping with Staffmark in Rock Hill, SC "excelent" wrote: =LEFT(ADDRESS(1,COLUMN(A1),4,1),FIND("1",ADDRESS(1 ,COLUMN(A1),4,1))-1) "Dave Birley" skrev: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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, -- Dave Temping with Staffmark in Rock Hill, SC "Mike H" wrote: 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 "Dave Birley" wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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!!! -- Dave Temping with Staffmark in Rock Hill, SC "Scoops" wrote: On 4 May, 13:27, Dave Birley wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
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 "Dave Birley" wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill ROW with alpha (a-z)
Delicious -- this will drive my wife nuts <g! Thanks.
-- Dave Temping with Staffmark in Rock Hill, SC "JLGWhiz" wrote: 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 "Dave Birley" wrote: 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,RO W(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. -- Dave Temping with Staffmark in Rock Hill, SC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i use sumproduct if my lookup table is alpha or alpha num? | Excel Worksheet Functions | |||
How fill cells with alpha series? | Excel Discussion (Misc queries) | |||
How do I sort alpha neumeric fields that have an alpha suffix? | Excel Worksheet Functions | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |