A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

Not as bad as you thought Larry. Of course you don't actually need the
message box bit you can simply use the value returned in another routine.

Sub FindLastCol()
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) 0 Then
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LastColumn
End If
End Sub


"LarryP" wrote:

A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message ...
A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

On the 19 of Jan, Chip posted this update in the group:

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function


--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message ...
A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

Thanks Tom

I copy it in my archive


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tom Ogilvy" wrote in message ...
On the 19 of Jan, Chip posted this update in the group:

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function


--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message ...
A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

Here is a one-liner:

Function col_idn(n As Integer) As String
col_idn = Split(Cells(1, n).Address, "$")(1)
End Function
--
Gary's Student
gsnu200703


"LarryP" wrote:

A pesky little thing is eluding me. If I determine that the last used column
in a worksheet is, say, Worksheet.Columns(22), how can I convert that into
"V" so I can subsequently do things to Cell V1? I'm sure there must be some
nifty Property out there somewhere for this purpose, but darn if I've been
able to find it. Surely this isn't going to take some gawdawful Select Case
statement!

Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure there
must be some nifty Property out there somewhere for this purpose, but darn
if I've been able to find it. Surely this isn't going to take some
gawdawful Select Case statement!

Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new function wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message ...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure there
must be some nifty Property out there somewhere for this purpose, but darn
if I've been able to find it. Surely this isn't going to take some
gawdawful Select Case statement!

True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine for
97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new function
wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure
there must be some nifty Property out there somewhere for this purpose,
but darn if I've been able to find it. Surely this isn't going to take
some gawdawful Select Case statement!

No problem for me Bob

In the Netherlands not many people use 97 but I believe in the States there
are a lot of people still using it ?

I use Tom's Split97 when I want to use it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message ...
True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine for
97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new function
wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure
there must be some nifty Property out there somewhere for this purpose,
but darn if I've been able to find it. Surely this isn't going to take
some gawdawful Select Case statement!

Ron,

Both this cluncky approach

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

and Chip's function appear measurably faster than Bob's use of Split. I
could only test out to 256 columns, but give it a try (if you like) and see
what you get in 2007. (I used the clunky timer, but the difference was
apparent.)

Here is what I used to test - again, it only test out to IV.

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function

Function ColumnLetterBob(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetterBob = sColumn
End Function

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

Sub Timeit()
Dim c As Range, l As String
Dim sngStart As Single
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetter(c)
Next
Next i
Debug.Print Timer - sngStart
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetterBob(c.Column)
Next
Next i
Debug.Print Timer - sngStart

sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = Cluncky(c)
Next
Next i
Debug.Print Timer - sngStart

End Sub

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

No problem for me Bob

In the Netherlands not many people use 97 but I believe in the States there
are a lot of people still using it ?

I use Tom's Split97 when I want to use it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message ...
True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine for
97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new function
wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure
there must be some nifty Property out there somewhere for this purpose,
but darn if I've been able to find it. Surely this isn't going to take
some gawdawful Select Case statement!

Hi Tom

Results in Excel 2007 on my machine the first time

0.0078125
0.0234375
0.0078125

Here a few times, you see that you see 0.0078125 only a few times

0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.0234375
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.0078125
0.0234375
0.0234375
0.015625
0.015625
0.03125
0.0078125
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.0234375
0.015625
0.015625
0.0234375
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.03125
0.015625
0.015625
0.0234375
0.015625
0.015625
0.0234375
0.0078125



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tom Ogilvy" wrote in message ...
Ron,

Both this cluncky approach

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

and Chip's function appear measurably faster than Bob's use of Split. I
could only test out to 256 columns, but give it a try (if you like) and see
what you get in 2007. (I used the clunky timer, but the difference was
apparent.)

Here is what I used to test - again, it only test out to IV.

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function

Function ColumnLetterBob(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetterBob = sColumn
End Function

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

Sub Timeit()
Dim c As Range, l As String
Dim sngStart As Single
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetter(c)
Next
Next i
Debug.Print Timer - sngStart
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetterBob(c.Column)
Next
Next i
Debug.Print Timer - sngStart

sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = Cluncky(c)
Next
Next i
Debug.Print Timer - sngStart

End Sub

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

No problem for me Bob

In the Netherlands not many people use 97 but I believe in the States there
are a lot of people still using it ?

I use Tom's Split97 when I want to use it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message ...
True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine for
97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new function
wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last used
column in a worksheet is, say, Worksheet.Columns(22), how can I convert
that into "V" so I can subsequently do things to Cell V1? I'm sure
there must be some nifty Property out there somewhere for this purpose,
but darn if I've been able to find it. Surely this isn't going to take
some gawdawful Select Case statement!

It would be even slower if used a home-grown Split in 97 :-)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Tom Ogilvy" wrote in message
...
Ron,

Both this cluncky approach

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

and Chip's function appear measurably faster than Bob's use of Split. I
could only test out to 256 columns, but give it a try (if you like) and
see
what you get in 2007. (I used the clunky timer, but the difference was
apparent.)

Here is what I used to test - again, it only test out to IV.

Function ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function

Function ColumnLetterBob(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetterBob = sColumn
End Function

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

Sub Timeit()
Dim c As Range, l As String
Dim sngStart As Single
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetter(c)
Next
Next i
Debug.Print Timer - sngStart
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetterBob(c.Column)
Next
Next i
Debug.Print Timer - sngStart

sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = Cluncky(c)
Next
Next i
Debug.Print Timer - sngStart

End Sub

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

No problem for me Bob

In the Netherlands not many people use 97 but I believe in the States
there
are a lot of people still using it ?

I use Tom's Split97 when I want to use it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine
for
97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new
function
wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last
used
column in a worksheet is, say, Worksheet.Columns(22), how can I
convert
that into "V" so I can subsequently do things to Cell V1? I'm sure
there must be some nifty Property out there somewhere for this
purpose,
but darn if I've been able to find it. Surely this isn't going to
take
some gawdawful Select Case statement!

Ron,
Thanks for checking it out.

This was really inspired by a discussion sometime ago. I believe it was
Charles Williams that pointed out that some form of the "clunky" method was
really among the fastest. He may have used a slightly faster version than
I came up with.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote in message
...
Hi Tom

Results in Excel 2007 on my machine the first time

0.0078125 0.0234375 0.0078125
Here a few times, you see that you see 0.0078125 only a few times

0.015625 0.03125 0.015625 0.015625 0.03125 0.015625 0.015625 0.0234375
0.015625 0.015625 0.03125 0.015625 0.015625 0.03125 0.015625 0.015625
0.03125 0.015625 0.015625 0.03125 0.015625 0.015625 0.03125 0.015625
0.015625 0.03125 0.015625 0.015625 0.03125 0.015625 0.015625 0.03125
0.0078125 0.0234375 0.0234375 0.015625 0.015625 0.03125 0.0078125 0.015625
0.03125 0.015625 0.015625 0.03125 0.015625 0.015625 0.0234375 0.015625
0.015625 0.0234375 0.015625 0.015625 0.03125 0.015625 0.015625 0.03125
0.015625 0.015625 0.03125 0.015625 0.015625 0.03125 0.015625 0.015625
0.0234375 0.015625 0.015625 0.0234375 0.0078125


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tom Ogilvy" wrote in message
...
Ron,

Both this cluncky approach Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

and Chip's function appear measurably faster than Bob's use of Split. I
could only test out to 256 columns, but give it a try (if you like) and
see what you get in 2007. (I used the clunky timer, but the difference
was apparent.)

Here is what I used to test - again, it only test out to IV. Function
ColumnLetter(R As Range) As String
ColumnLetter = Left(R.Address(False, False), _
1 - (R.Column 26) - (R.Column 702))
End Function

Function ColumnLetterBob(col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetterBob = sColumn
End Function

Function Cluncky(c As Range)
s = c.Address(0, 0)
l = c.Column
If l <= 26 Then
Cluncky = Left(s, 1)
ElseIf l <= 702 Then
Cluncky = Left(s, 2)
Else
Cluncky = Left(s, 3)
End If
End Function

Sub Timeit()
Dim c As Range, l As String
Dim sngStart As Single
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetter(c)
Next
Next i
Debug.Print Timer - sngStart
sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = ColumnLetterBob(c.Column)
Next
Next i
Debug.Print Timer - sngStart

sngStart = Timer
For i = 1 To 10
For Each c In Range("A1:IV1")
l = Cluncky(c)
Next
Next i
Debug.Print Timer - sngStart

End Sub

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote:

No problem for me Bob

In the Netherlands not many people use 97 but I believe in the States
there
are a lot of people still using it ?

I use Tom's Split97 when I want to use it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
True, anyone still using 97? <g

It works for me because I have a conditionally compiled Split routine
for 97.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi Bob

Thanks, but

As far as I know your function will not work in 97 and Chip's new
function wil also work in 97.

Ron


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bob Phillips" wrote in message
...
Here's an alternative that works in all versions Ron

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ron de Bruin" wrote in message
...
Hi LarryP

You can use Cells(R,C) if you use the number

With Sheets("Sheet1")
.Cells(1, 22).Value = "Hi"
End With

Another way is to use the Function from Chip Pearson
Not working in Excel 2007

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber 26))
End Function


Sub test3()
MsgBox ColumnLetter(22)
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LarryP" wrote in message
...
A pesky little thing is eluding me. If I determine that the last
used column in a worksheet is, say, Worksheet.Columns(22), how can
I convert that into "V" so I can subsequently do things to Cell V1?
I'm sure there must be some nifty Property out there somewhere for
this purpose, but darn if I've been able to find it. Surely this
isn't going to take some gawdawful Select Case statement!