Excel Howto?

-Aviral Mittal

Note: You can cut+paste the whole code into your VBA Editor.

'1. Here is a function which will return the column address for a keyword in a row.

Function FindColAddr(ColumnHeading As String, Optional RowNum As Long, Optional shtName As String) As String
'This function will return the address corresponding to a named heading.
'Usually the function will assume that the named heading string is in Row 1
'Though it does gives an option to find it in any row.
'The Column ID is returned as an integer(long).
'In case of error, Column ID is 0.
'It will search for only 1 Occurance, if more are found it reports error.
'If the function finds multiple occurences of 'ColumnHeading' its an error as only 1 is allowed.
'Example 1 : colNum = FindColAddr("MYSTRING", 1, "Sheet1"), where colNum is of type long
'Example 2 : colNo = FindColAddr(ColumnHeading:="MYHEADING", RowNum:=1, shtName:="Lists")
'Example 3 : colNo = FindColAddr(ColumnHeading:="COUNTRY_NAME", shtName:="Lists")
  Dim sht As Worksheet
  Dim mCell As Range
  If (shtName = "") Then
    shtName = "Sheet1"
  End If
  On Error GoTo errorHandler
  Set sht = ThisWorkbook.Worksheets(shtName)
 
 
  sht.Activate
  If RowNum = 0 Then
    RowNum = 1
  End If
 
  Set mCell = Rows(RowNum).Find(What:=ColumnHeading, lookat:=xlWhole) 'searchStr is the string which is being searched
  If mCell Is Nothing Then
    Debug.Print "ERROR! From Function 'FindColAddr': ColumnHeading " & "'" & ColumnHeading & "'" & " not found in sheet '" & shtName & "'"
    MsgBox "ERROR! From Function 'FindColAddr': ColumnHeading " & "'" & ColumnHeading & "'" & " not found in sheet '" & shtName & "'"
    Exit Function
  Else
    FindColAddr = mCell.Address
    Debug.Print "INFO: From Function 'FindColAddr': Found '" & ColumnHeading & "' Address = " & mCell.Address & " In Sheet '" & shtName & "'"
  End If
 
  Dim firstCellAddress As String
  firstCellAddress = mCell.Address
  Dim ii As Long
  ii = 0
  Do
    ii = ii + 1
    If (ii > 1) Then
      Debug.Print "ERROR! Sheet " & "'" & shtName & "'" & " has multiple occurence of " & "'" & ColumnHeading & "' at: " & mCell.Address
      MsgBox "ERROR! Sheet " & "'" & shtName & "'" & " has multiple occurence of " & "'" & ColumnHeading & "' at: " & mCell.Address
      FindColAddr = 0
    End If
    Set mCell = Rows(RowNum).FindNext(mCell)
  Loop While firstCellAddress <> mCell.Address

  Exit Function
errorHandler:
  Debug.Print "From Function 'FindColAddr': Sheet " & "'" & shtName & "'" & " Does not exist"
  MsgBox "From Function 'FindColAddr': Sheet " & "'" & shtName & "'" & " Does not exist"

End Function


'2. Now convert the found column address to Column Number
Function getColNum(ColumnHeading As String, Optional RowNum As Long, Optional shtName As String) As Long
  On Error Resume Next
  getColNum = Range(FindColAddr(ColumnHeading:=ColumnHeading, RowNum:=RowNum, shtName:=shtName)).Column
End Function


'3. Now convert the found column address to Column Character


Function getColChar(ColumnHeading As String, Optional RowNum As Long, Optional shtName As String) As String
  'getColChar = Split(mStr, "$")(1)
  On Error Resume Next
  getColChar = Split(FindColAddr(ColumnHeading:=ColumnHeading, RowNum:=RowNum, shtName:=shtName), "$")(1)
End Function
'4. How to use:
'Example:

  Dim colNo As String
  colNo = FindColAddr(ColumnHeading:="Company")
  If (colNo <> "") Then
    MsgBox "INFO! From Sub 'TryFindColAddr' : Found column Address = for 'Company' as " & colNo
  End If


Download Excel Book containing the Above code here:
Book2.xlsm

<- Previous                                                                                                     Next ->