Solved

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

Posted on 2012-12-29
3
651 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!
0
Comment
Question by:Last_Free_Man
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38730043
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
0
 

Author Comment

by:Last_Free_Man
ID: 38730424
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!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38730614
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.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question