Solved

vba find column number based on header value

Posted on 2014-10-22
5
5,719 Views
Last Modified: 2014-10-22
I wondering if there is a way I can get the column number based on a header value in row 1.   I am currently looping through  columns but the columns keep moving in the file that is being used.   The below is a sample of the code but instead defining it as column 5, I would like to find Customer Lead in row 1 and then get the column number.

WS.Range("D8").Value = wsData.Cells(i, 5).Value    'Customer Lead 

Open in new window


Thanks,
Montrof
0
Comment
Question by:montrof
[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
  • 3
5 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40396703
You can use the worksheet function MATCH within a VBA script to get the column number:

ColOffset = Application.WorksheetFunction.MATCH("Customer Lead",Sheet1!$1:$1,0)

Thanks
Rob H
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 40396718
Thank you very much

Montrof
0
 
LVL 1

Author Comment

by:montrof
ID: 40396735
Sorry one more question is there an easy way to skip the search if the value is not found.
0
 
LVL 34

Expert Comment

by:Norie
ID: 40396774
If the header might not be there you could try something like this.
Dim Res As Variant 

    Res = Application.Match("Customer Lead", Sheets("Sheet1").Rows(1), 0)

    If IsError(Res) Then 
        MsgBox "Customer Lead column not found."
        Exit Sub
    End If
    
     ' continue with code
    WS.Range("D8").Value = wsData.Cells(i, Res).Value

Open in new window

0
 
LVL 1

Author Comment

by:montrof
ID: 40397007
Thanks Again
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

734 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