- rng(rows(5), rows(7)).Select ' Works fine
- rng(Columns(3), Columns(5)).Select ' Fails
Function ColLtrFromNum(ColNum As String, _ Optional wkbk As Workbook, _ Optional wksht As Worksheet) As String On Error GoTo ColLtrFromNum_Error If wksht Is Nothing Then If wkbk Is Nothing Then Set wksht = ThisWorkbook.Worksheets(1) Else Set wksht = wkbk.Worksheets(1) End If End If If ColNum >= 1 And ColNum <= wksht.Columns.Count Then ColLtrFromNum = wksht.Cells(1, CInt(ColNum)).Address(1, 0) ColLtrFromNum = Left(ColLtrFromNum, InStr(ColLtrFromNum, "$") - 1) Else ColLtrFromNum_Error: ColLtrFromNum = "#ERROR" End If On Error GoTo 0 End Function '**************************** ' Test Sub Sub textCLFM() Dim v, w, x, y, z, a, b v = ColLtrFromNum("JHOI") w = ColLtrFromNum("15,767") x = ColLtrFromNum(0) y = ColLtrFromNum(57.5999) z = ColLtrFromNum(" 57 ") a = ColLtrFromNum _ (ThisWorkbook.Worksheets(1).Columns.Count) b = ColLtrFromNum("") End Sub
Explanatory NotesThe ColNum parameter is declared as a String instead of a numeric data type to allow returning an error of "#VALUE" instead of having the function fail to complete. This also allows for external error checking and use as a worksheet function.
The purpose of the Optional Workbook and Worksheet parameters, coupled with the opening IF structure, is to facilitate use with Excel 2007/2010, workbooks from earlier releases, and those pesky worksheets that have been copied/moved from earlier releases into Excel 2007/2010 workbooks.
The CInt function prevents errors from numeric strings. Some may choose CDbl as a safety in case MicroSoft moves to a worksheet more than twice as wide as present, something I don't think will happen soon.
UsesFour uses come to mind, mainly because A1 notation is easier for people, i.e. Range( ColLtrFromNum(<col>) & <row>) versus Range(Cells(<row1>,<col1>))
- Coding a Range description
- When using the Worksheet Indirect function, e.g. the following returns what is five columns to the right.
=Indirect(ColLtrFromNum(Column()+5 & Row())
- In a Watch so that you can see the address and the value, e.g. ColLtrFromNum(colnumSource) & rownumSource ": " & Cells(rownumSource,colnumSource).Value
- When reporting the location of a value, e.g. in a message box for trouble shooting where column letters are not part of the design
MsgBox "Error cause by value at cell " ws.Name & "!" & ColLtrFromNum(colnumSource) & rownumSource & ": " & ws.Cells(rownumSource,colnumSource).Value