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

Column Number/Letter Substitute
 

one way

Range("a19:" & Chr(j + 64) & "26").Select


---
Message posted from http://www.ExcelForum.com/

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/

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

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

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/

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

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/

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!

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

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