|
Friday, September 25, 2009 - 1:06 PM
These functions facilitate using the extremely useful built-in 'match' function to identify column or row numbers for a supplied header. These functions accomplish the same objective as using named ranges for each column, but in an easier and better way. Header names may also be stored as public variables in your declarations module to prevent typos.
These functions can be used to identify column and row numbers to use for filtering, assuming there are no empty column or row headers in the filter range. (Also see "Reliable Worksheet Filtering With VBA".) Check back soon for code to account for missing column and row headers.
You may prefer to hardcode the header row range name rather than using the FindRange function for sake of speed; or, if all of your sheets' headers are in the same row, then hard code the row number. That would improve speed, but using FindRange makes the functions more flexible.
Option Compare Text
Public Function cMatch&(ColHeader$, Optional ByVal WSName$, Optional ByVal WBName$, Optional ByVal HeaderRowNum&)
'returns column number for a given header (to avoid hardcoding same information)
'returns 0 if not found instead of an error
'using Option Compare Text
On Error Resume Next
If Not TestLen(WBName) Then WBName = ActiveWorkbook.Name
If Not TestLen(WSName) Then WSName = Workbooks(WBName).ActiveSheet.Name
If IsMissing(HeaderRowNum) Then
Dim hdr As Range: hdr = FindRange("HeaderRow", WSName, WBName): HeaderRowNum = hdr.Row
End If
cMatch = Application.Match(ColHeader, Workbooks(WBName).Sheets(WSName).Rows(HeaderRowNum), 0)
End Function
Public Function rMatch&(RowHeader$, Optional ByVal WSName$, Optional ByVal WBName$, Optional ByVal HeaderColNum&)
'returns row number for a given header (to avoid hardcoding same information)
'returns 0 if not found instead of an error
'using Option Compare Text
On Error Resume Next
If Not TestLen(WBName) Then WBName = ActiveWorkbook.Name
If Not TestLen(WSName) Then WSName = Workbooks(WBName).ActiveSheet.Name
If IsMissing(HeaderRowNum) Then
Dim hdr As Range: hdr = FindRange("HeaderColumn", WSName, WBName): HeaderColNum = hdr.Column
End If
cMatch = Application.Match(ColHeader, Workbooks(WBName).Sheets(WSName).Columns(HeaderColNum), 0)
End Function
|
|
| << Navigate to Friday, September 25, 2009 |
Add New Comment |