Solved

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

Posted on 2012-12-29
3
642 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now