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" <(E-Mail Removed)> wrote in message news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message news:3B1FAD15-9462-44C1-9303-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
news:O%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:O%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>>>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:O%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>>>>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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:O%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:9A02F27F-1079-41B3-91CB-(E-Mail Removed)...
>>>>>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!
>>>
>