Column Number/Letter Substitute

  • Thread starter Thread starter John Wilson
  • Start date Start date
J

John Wilson

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
 
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
 
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
 
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/
 
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
 
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
 
Back
Top