Solved: How to increase letters

[pedrovarela]

Hi,

I have data for different months starting from different columns. I would like to use the same routine just changing the reference column. For instance Column D +1,etc... How can I increase a letter?



col = "D"

col = col + 1

[pedrovarela]

In the meanwhile, I found the solution! Nice trick.


Function ColNo2ColRef(ColNo As Integer) As String
If ColNo < 1 Or ColNo > 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function
The function below converts a column reference (A - IV) to a column number between 1 and 256:
Function ColRef2ColNo(ColRef As String) As Integer
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = Range(ColRef & "1").Column
End Function

[mikerickson]

If you are using it to refer to cells, just use numbers instead of letters and the Cells property.

Range("C2") Is Range("C" & 2) Is Cells(2,"C") Is Cells(2,3)

Range("C2:F14") Is Range(Range("C2"),Range("F14")) Is Range(Cells(2,3),Cells(14,6))

[pedrovarela]

Hi Mike,

Nice! Thanks for the help!

Cheers,