Solved

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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro to change number to Month in Excel? 10 47
Set WorkSheet  not Working 9 42
Highlight changing numbers in column 3 20
Consolidation of Worksheet into a final worksheet 4 40
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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

829 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