Column/Range: macro for converting from numeric to alphanumeric format

[Archangelos]

It is very convenient to handle columns in numeric format than in alpabetical format, i.e. it's to write cells(1,2) instead of cells("B1").

However, the bloody ranges work work only in alphanumeric format. I wrote some code for converting either columns or a range from numeric format to alphanumeric.

Function ColumnNumToAlpha(Arithmos As Integer) As String 'Use these to convert a column from numeric to alphanumeric


Select Case Arithmos
       Case Is < 1
            ColumnNumToAlpha = "Column number ranges from 1 to 16384"
       Case Is > 16384
            ColumnNumToAlpha = "Column number ranges from 1 to 16384"
       Case Else


            If Arithmos > 702 Then
               ColumnNumToAlpha = AlphanumericTria(Arithmos)
            Else
               If Arithmos > 26 Then
                  ColumnNumToAlpha = AlphanumericDyo(Arithmos)
               Else
                  ColumnNumToAlpha = AlphanumericEna(Arithmos)
               End If
            End If
End Select


End Function






Function RangeNumToAlpha(LeftCol As Integer, UpRow As Integer, RightCol As Integer, LowRow As Integer) As String 'Use these to convert a range from numeric to alphanumeric


Dim UpRowFlag As Integer
Dim LowRowFlag As Integer
Dim LeftColFlag As Integer
Dim RightColFlag As Integer


        If UpRow < 1 Then
           UpRowFlag = 1
        Else
           If UpRow > 65536 Then
              UpRowFlag = 1
           Else
              UpRowFlag = 0
           End If
        End If


        If LowRow < 1 Then
           LowRowFlag = 1
        Else
           If LowRow > 65536 Then
              LowRowFlag = 1
           Else
              LowRowFlag = 0
           End If
        End If


        If LeftCol < 1 Then
           LeftColFlag = 1
        Else
           If LeftCol > 65536 Then
              LeftColFlag = 1
           Else
              LeftColFlag = 0
           End If
        End If
        
        If RightCol < 1 Then
           RightColFlag = 1
        Else
           If RightCol > 65536 Then
              RightColFlag = 1
           Else
              RightColFlag = 0
           End If
        End If




       If UpRowFlag + LowRowFlag + LeftColFlag + RightColFlag > 0 Then
          RangeNumToAlpha = "Column number ranges from 1 to 16384, Row number ranges from 1 to 65536"
       Else
          RangeNumToAlpha = ColumnNumToAlpha(LeftCol) & UpRow & ":" & ColumnNumToAlpha(RightCol) & LowRow
       End If
       
End Function










Function AlphanumericEna(StiliArithmos As Integer) As String 'Auxilliary function
         AlphanumericEna = Gramma(StiliArithmos - 1)
End Function


Function AlphanumericDyo(StiliArithmos As Integer) As String 'Auxilliary function
         AlphanumericDyo = Gramma(((StiliArithmos - 27) \ 26)) & Gramma((StiliArithmos - 27) Mod 26)
End Function




Function AlphanumericTria(StiliArithmos As Integer) As String 'Auxilliary function
Dim Psifio0 As Integer
Dim Psifio1 As Integer
Dim Psifio2 As Integer




         Psifio2 = (StiliArithmos - 703) \ 676
         Psifio1 = ((StiliArithmos - 703) - (Psifio2 * 676)) \ 26
         Psifio0 = (StiliArithmos - 703) - (Psifio2 * 676) - (Psifio1 * 26)
         AlphanumericTria = Gramma(Psifio2) & Gramma(Psifio1) & Gramma(Psifio0)
End Function












Function Gramma(Noumero As Integer) As String 'Auxilliary function


Select Case Noumero
       Case Is = 0
            Gramma = "A"
       Case Is = 1
            Gramma = "B"
       Case Is = 2
            Gramma = "C"
       Case Is = 3
            Gramma = "D"
       Case Is = 4
            Gramma = "E"
       Case Is = 5
            Gramma = "F"
       Case Is = 6
            Gramma = "G"
       Case Is = 7
            Gramma = "H"
       Case Is = 8
            Gramma = "I"
       Case Is = 9
            Gramma = "J"
       Case Is = 10
            Gramma = "K"
       Case Is = 11
            Gramma = "L"
       Case Is = 12
            Gramma = "M"
       Case Is = 13
            Gramma = "N"
       Case Is = 14
            Gramma = "O"
       Case Is = 15
            Gramma = "P"
       Case Is = 16
            Gramma = "Q"
       Case Is = 17
            Gramma = "R"
       Case Is = 18
            Gramma = "S"
       Case Is = 19
            Gramma = "T"
       Case Is = 20
            Gramma = "U"
       Case Is = 21
            Gramma = "V"
       Case Is = 22
            Gramma = "W"
       Case Is = 23
            Gramma = "X"
       Case Is = 24
            Gramma = "Y"
       Case Is = 25
            Gramma = "Z"
       Case Else
            'Nothing else to be done


End Select




End Function

How do you like it?

 

[Fluff]

Wouldn't it be easier just to use something like

Columns(1).Font.Size = 14
Cells(1, 1).Font.Color = vbGreen
Range(Cells(2, 1), Cells(3, 1)).Font.Color = vbRed

 

[Archangelos]

I dont't understand what you are talking about.

 

[Rick Rothstein]

It is very convenient to handle columns in numeric format than in alpabetical format, i.e. it's to write cells(1,2) instead of cells("B1").

However, the bloody ranges work work only in alphanumeric format. I wrote some code for converting either columns or a range from numeric format to alphanumeric.

I am not sure it this will help you with your perceived problem or not, but are you aware that the Cells property can take the column designation as either its column number or its letter designation? For example, these both refer to cell B3...

Cells(3, 2)

Cells(3, "B")

And to expand the range from them, you can use the Resize property. For example, these both would refer to the range B3:F6

Cells(3, 2).Resize(4, 5)

Cells(3, "B").Resize(4, 5)

 

[JackDanIce]

Is this post related to .Address property?

E.g. Cells(1, 1).Address? And then Cells.Address(0, 0), Cells.Address(1, 0), Replace(Cells(1, 1).Address, "$", "") etc?

Or .Column? It's confusing, what's the point of so much code for something relatively short and straight forward?

Afraid do not see the point of it, so to answer question.. sorry, no don't like it!

 

[Rick Rothstein]

As a follow up to what I wrote in Message #4 above, I thought you might it interesting to know that your first two functions can be written as one-liner functions as shown below...

[table="width: 500"]
[tr]
	[td]Function ColumnNumToAlpha(Arithmos As Long) As String
  ColumnNumToAlpha = Replace(Cells(1, Arithmos).Address(0, 0), 1, "")
End Function

Function RangeNumToAlpha(LeftCol As Long, UpRow As Long, RightCol As Long, LowRow As Long) As String
  RangeNumToAlpha = Range(Cells(UpRow, LeftCol), Cells(LowRow, RightCol)).Address(0, 0)
End Function[/td]
[/tr]
[/table]

 

[Archangelos]

The point is that it is very convenient to me using numbers for the columns than letters. However, a time came that I had to use a range thing, something like Range("A1:F6"). So, I had to convert the number to letter format.

For example, column number 27 is column "AA".

And to expand the range from them, you can use the Resize property. For example, these both would refer to the range B3:F6

Cells(3, 2).Resize(4, 5)

Cells(3, "B").Resize(4, 5)

Well, that resize thing looks interesting and useful. I was not aware of that and that's the reason I made my own code.

It seems that I wrote the code in vain.

 

[Fluff]

As well as using Resize you can write Range("A1:F6") like
Range(Cells(1, 1), Cells(6, 6)).Select

 

[Rick Rothstein]

As well as using Resize you can write Range("A1:F6") like
Range(Cells(1, 1), Cells(6, 6)).Select

Or like this as well...

Range(Cells(1, "A"), Cells(6, "F")).Select

or, using Resize, like this...

Cells(1, 1).Resize(6, 6).Select

or like this...

Cells(1, "A").Resize(6, 6).Select

or, of course, like this...

Range("A1").Resize(6, 6).Select

It all depends on what you have and what your needs are for implementing it.

 

[Fluff]

Not forgetting
Intersect(Rows("1:6"), Range("A:F")).Select