Say you’re working with columns and need to use the number rather than the letter to work with one of your loops or vide-versa. These work beautifully for this. 

Function ConvertToNumber(sCol As String) As Long
ConvertToNumber = Range(sCol & 1).Column
End Function


Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iRemainder >= 27 Then
iAlpha = iAlpha + 1
iRemainder = iRemainder - 26
End If
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s