Igor Spryzhkov
asked on
Excel VBA: search column number by certain cell's value
Hello!
I have Worksheet with headers and data.
Header (1st row):
A1: actv_code_type_id
B1: actv_short_len
C1: seq_num
D1: actv_code_type
E1: proj_id
F1: wbs_id
G1: actv_code_type_scope
2st and following rows contants various date.
What I need: find certain string in header's row and get column number.
Yes, of course, I can make loop for examine each column in first row:
... but maybe there is better (more intelligent and elegant) way?
Thanks!
I have Worksheet with headers and data.
Header (1st row):
A1: actv_code_type_id
B1: actv_short_len
C1: seq_num
D1: actv_code_type
E1: proj_id
F1: wbs_id
G1: actv_code_type_scope
2st and following rows contants various date.
What I need: find certain string in header's row and get column number.
Yes, of course, I can make loop for examine each column in first row:
Function getFieldColumn(s As Worksheet, ByVal sName As String) As Integer
Dim iCol As Integer
iCol = 1
While s.Cells(1, iCol) <> ""
If UCase(s.Cells(1, iCol)) = UCase(sName) Then
getFieldColumn = iCol
Exit Function
End If
iCol = iCol + 1
Wend
End Function
... but maybe there is better (more intelligent and elegant) way?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, Last_Free_Man!
1. Why "LookIn:=xlFormulas,"? (not LookIn:=xlValues)
Good question - probably because I had just finished working on something where it was important to use formulas rather than values.
2. Please explain how to setup limit for search only in 1st (or in Nst) row?
In the Find statement, Range("1:1") already limits it to row 1 - simply change the "1:1" to whatever address you wish to use instead.
3. How to make Excel 2003 compatible code?
I don't see anything in my code that would cause issues with 2003. Are you getting an error?
Regards,
Brian.
1. Why "LookIn:=xlFormulas,"? (not LookIn:=xlValues)
Good question - probably because I had just finished working on something where it was important to use formulas rather than values.
2. Please explain how to setup limit for search only in 1st (or in Nst) row?
In the Find statement, Range("1:1") already limits it to row 1 - simply change the "1:1" to whatever address you wish to use instead.
3. How to make Excel 2003 compatible code?
I don't see anything in my code that would cause issues with 2003. Are you getting an error?
Regards,
Brian.
ASKER
Very nice code! Thanks!
Quetions:
1. Why "LookIn:=xlFormulas,"? (not LookIn:=xlValues),
2. Please explain how to setup limit for search only in 1st (or in Nst) row?
3. How to make Excel 2003 compatible code?
Thanks again!