PDA

View Full Version : [SOLVED] UDF to convert column Letter to Number and return the Largest column as Number



10shlomi10
12-23-2016, 06:19 PM
Hi

I need help with UDF please.
I have the a cells range with columns letters.

I want to build a UDF that will return the highest column latter BUT as A Number (what is called: Excel column number index).

I start writing something but I'm stack ...


Public Function MaxColNum(ByRef rng As Range, ColNm As Variant)'convert column Letter to Number and find the Largest column
LastCol = ActiveSheet.Cells("A", Columns.Count).End(xlToLeft).Column
lastColLetter = Range(LastCol, LastCol & 1).Column

Set rng = Range("C2:" & lastColLetter & Rows.Count)

For Each ColNm In rng
MaxColNum = Range(ColNm, ColNm & 1).Column
Next

'MaxwColNm = ......


End Function




C2 is the first cell to search till the end of rows+end of columns

17898

Thanks !

SamT
12-24-2016, 07:54 AM
If ColNm > BiggestLetter Then BiggestLetter = ColNm
Next ColNm
MaxColNum = Cells(1, BiggestLetter).Column

10shlomi10
12-24-2016, 10:15 AM
If ColNm > BiggestLetter Then BiggestLetter = ColNm
Next ColNm
MaxColNum = Cells(1, BiggestLetter).Column


Thanks!
but....
From where the variable BiggestLetter came from ? and How the code can know what is the BiggestLetter is ? as this is the exactly thing (the Largest Column Letter) I want to find....

appreciate your explanation.

10shlomi10
12-24-2016, 11:29 AM
Got it work.

Here's the code:

Sub MaxColNum()Dim rng As Range
'convert column Letter to Number and find the Largest column
Dim LastColumn As Long
Dim LastRow As Long

With ActiveSheet.UsedRange
LastColumn = .Columns(.Columns.Count).Column
LastRow = .Rows(.Rows.Count).row
End With

'Clear the 'cell Named MaxLC. in the end of the the BIGGEST LETTER will be placed on this cell
Range("MaxLC") = ""

'set the Range to Loop
Set rng = Range(Cells(2, 3), Cells(LastRow, LastColumn))

BiggestLetter = 0


For Each ColN In rng
If ColN <> "" Then
'Convert Column Letter into column Number
'and load the number into ColN variable
ColN = Range(ColN & 1).Column
If ColN > BiggestLetter Then BiggestLetter = ColN
End If
Next ColN


'Placed the BiggestLetter found into cell Named: MaxLC
Range("MaxLC") = BiggestLetter


End Sub

SamT
12-24-2016, 05:22 PM
:thumb