Transformation "number->letter" for column's name 1

[elbehh]

Hello, hello !

Knows somebody a function in VBA who transform a number(digit) in a letters to use for column's name?
The problem is this:
I have a function
Private Sub Bord(stri As String, m As Integer, gros As Integer)
Dim lat As Variant
lat = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
For i = 0 To m
With Range(stri).Borders(lat(i))
.LineStyle = xlContinuous
.Weight = gros
End With
Next i
End Sub

I call this function many times in diferent ways
Call Bord("B9:BF" & Maximul + 12, 3, 1) '-------- for a fixed range
. . . . . . . . . . . . . .
Call Bord(Chr(ColNr+65) & RowNr) '------- for a cell

I tried this, for a variable range and it's not working
Call Bord(Range(Cells(x1, y1), Cells(x2, y2)), 3, 2)

Normally, Cells(x1, y1) gave me the value from this cell, not the address ...
I found a way to use the same solution what I used for cell ... Chr(ColNr+65) ... but it's not working for ColNr > 26
I have to make separately an algorithm ... "if is more then 26 put the name for columns with A in the beginning" and so on
Maybe there is an elegant solution .... with a VBA's function (digit -> letter) ... or other solution who give me the address of cells, not the value

I know it's not a difficult problem, excuse me if it's already disscussed ... I spent a lot of time trying to find in forum, but without succes

Thanks !

 

[PHV]

You may try this:
Call Bord(Range(Cells(x1, y1), Cells(x2, y2))[!].Address(False, False)[/!], 3, 2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[elbehh]

Hello PHV
It was so simple !! ... ahh, I spent 2 days ....
At least I learned something: "if I don't know, I have to ask !"
Thank you for your lesson !