Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
A quickie....
This is what I have... Range("B19:J26").Select I have a variable "MyVal" = 10 I need to modify the formula to substitute the 10 in place of the "J" Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
one way
Range("a19:" & Chr(j + 64) & "26").Select --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
mudraker,
Wasn't what I was looking for. More like: Range("A19:" & Col(10) & "26").Select Of course, the above doesn't work. The 10 is a variable that can change. John "mudraker " wrote in message ... one way Range("a19:" & Chr(j + 64) & "26").Select --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
John,
I have shown two examples below. Hope they help. Sub test() Dim MyVal As Integer MyVal = 10 ' method 1 using "Chr" function ' Note: Chr(MyVal + 64) returns "J". Range("A19:" & Chr(MyVal + 64) & "26").Select ' method 2 using "Cells" property. ' Note: Cells parameters are "Row, Column". Range(Range("A19"), Cells(26, MyVal)).Select End Sub regards, John in Brisbane "John Wilson" wrote in message ... mudraker, Wasn't what I was looking for. More like: Range("A19:" & Col(10) & "26").Select Of course, the above doesn't work. The 10 is a variable that can change. John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
Range(Range("B19"),Cells(26, MyVal))
"John Wilson" wrote in message ... A quickie.... This is what I have... Range("B19:J26").Select I have a variable "MyVal" = 10 I need to modify the formula to substitute the 10 in place of the "J" Thanks, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
What if j refers to column 200?
"mudraker " wrote in message ... one way Range("a19:" & Chr(j + 64) & "26").Select --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
' Note: Chr(MyVal + 64) returns "J".
Range("A19:" & Chr(MyVal + 64) & "26").Select This method fails when the column reference is greater than column Z. "JohnI in Brisbane" wrote in message ... John, I have shown two examples below. Hope they help. Sub test() Dim MyVal As Integer MyVal = 10 ' method 1 using "Chr" function ' Note: Chr(MyVal + 64) returns "J". Range("A19:" & Chr(MyVal + 64) & "26").Select ' method 2 using "Cells" property. ' Note: Cells parameters are "Row, Column". Range(Range("A19"), Cells(26, MyVal)).Select End Sub regards, John in Brisbane "John Wilson" wrote in message ... mudraker, Wasn't what I was looking for. More like: Range("A19:" & Col(10) & "26").Select Of course, the above doesn't work. The 10 is a variable that can change. John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
Tim,
Good point. Please ignore my susggestion, using "Chr". regards, John in Brisban "Tim Zych" wrote in message ... What if j refers to column 200? "mudraker " wrote in message ... one way Range("a19:" & Chr(j + 64) & "26").Select --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
Hi John,
Try This, Sub Test1() myVar = 10 ' 0 and <= max. col. 256 vCol = Left(Columns(myVar).Address(, 0), IIf(myVar 27, 2, 1)) Range("B19:" & vCol & "26").Select End Sub Sub Test2() Dim rng As Range Set rng = Range("B19") myVar = 10 LRow = 26 vCol = Left(Columns(myVar).Address(, 0), IIf(myVar 27, 2, 1)) Range(rng, Range(vCol & LRow)).Select End Sub Sub Test3() Dim rng As Range Set rng = Range("B19") myVar = 10 LRow = 26 rng.Resize(LRow - rng.Row + 1, myVar - rng.Column +1).Select End Sub Regards, Shah Shailesh http://members.lycos.co.uk/shahweb/ *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
Tim,
Thanks to you and all who replied. Your suggestion was concise and easy to understand so that's the one I'll end up using. John "Tim Zych" wrote in message ... Range(Range("B19"),Cells(26, MyVal)) "John Wilson" wrote in message ... A quickie.... This is what I have... Range("B19:J26").Select I have a variable "MyVal" = 10 I need to modify the formula to substitute the 10 in place of the "J" Thanks, John |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number/Letter Substitute
In a thread last year, Tom Ogilvy suggested:
where i is a variable containing the column number -- left(columns(i).Address(0,0), 2 + (i < 27)) will return the column letter -- HTH, Dianne In , Tim Zych typed: ' Note: Chr(MyVal + 64) returns "J". Range("A19:" & Chr(MyVal + 64) & "26").Select This method fails when the column reference is greater than column Z. "JohnI in Brisbane" wrote in message ... John, I have shown two examples below. Hope they help. Sub test() Dim MyVal As Integer MyVal = 10 ' method 1 using "Chr" function ' Note: Chr(MyVal + 64) returns "J". Range("A19:" & Chr(MyVal + 64) & "26").Select ' method 2 using "Cells" property. ' Note: Cells parameters are "Row, Column". Range(Range("A19"), Cells(26, MyVal)).Select End Sub regards, John in Brisbane "John Wilson" wrote in message ... mudraker, Wasn't what I was looking for. More like: Range("A19:" & Col(10) & "26").Select Of course, the above doesn't work. The 10 is a variable that can change. John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
replace or substitute a letter for a specific number | Excel Discussion (Misc queries) | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Auto number w/ different letter-number combos in same column | Excel Worksheet Functions |