[AccessD] Manipulating Excel Cells

Hi John and Stuart

But where would you need that? And if, one line of code will do:

  lngCol = 53
  strCol = Left(wks.Columns(lngCol).Address(False, False), 1 + Sgn(lngCol \ 27))

  strCol = "BA"
  lngCol = wks.Columns(strCol).Column

/gustav

>>> stuart at lexacorp.com.pg 01-02-2006 23:49:07 >>>
On 1 Feb 2006 at 13:41, John Colby wrote:
> 
> So what I was looking for (and found) was a syntax for using column letters
> instead of the silly cell(31,29).  What column is 29?  What "number" is
> column BA?  

In case you need it in future:

Function ColNumToName(ColNum As Long) As String
   If ColNum < 27 Then
       ColNumToName = Chr$(ColNum + 64)
   Else
       ColNumToName = _
          Chr$(Int((ColNum - 1) \ 26) + 64) _
          + Chr$((ColNum - 1) Mod 26 + 65)
   End If
End Function

Function ColNameToNum(ColName As String) As Long
If Len(ColName) = 1 Then
ColNameToNum = Asc(UCase$(ColName)) - 64
Else
ColNameToNum = 26 * (Asc(UCase$(Left$(ColName, 1))) _
     - 64) + Asc(UCase$(Right$(ColName, 1))) - 64
End If
End Function
-- 
Stuart