Solved

Excel VBA: search column number by certain cell's value

Posted on 2012-12-29

Microsoft Excel

1 solution

Medium Priority

677 Views

Last Modified: 2012-12-30

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:

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

Open in new window

... but maybe there is better (more intelligent and elegant) way?

Thanks!

Comment

Question by:Last_Free_Man

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 26

Accepted Solution

by:redmondb

redmondb earned 2000 total points

ID: 387300432012-12-29

Hi, Last_Free_Man.

Please see attached. The code is...

Function getFieldColumn_II(s As Worksheet, ByVal sName As String) As Integer
Dim xFound As Range

Set xFound = s.Range("1:1").Find(What:=sName, LookIn:=xlFormulas, LookAt:=xlWhole _
                    , SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not xFound Is Nothing Then getFieldColumn_II = xFound.Column

End Function

Open in new window

Regards,
Brian.Find-Header.xlsm

Author Comment

by:Last_Free_Man

ID: 387304242012-12-29

Hello, Brain!

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!

LVL 26

Expert Comment

by:redmondb

ID: 387306142012-12-30

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.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel code to find and report on links in the selected ActiveWorkbook (formulas, range names, pivotTables & charts)

Article by: Dave

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…