Solved

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

Posted on 2012-12-29

Microsoft Excel

1 solution

671 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 500 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

Connect to SAP R/3 and call custom FM from MS Excel

Article by: ExcelGuide

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

A Game of Draw Poker

Article by: Martin

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.

Using, Creating and Modifying Styles in Microsoft Excel

Video by: Bob

Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

How to use the IF function in Excel

Video by: Bob

Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…