Searching a row for a value and returning that column

[zapp7]

Example:

A B C D E F
3 4 5 6 7 8

I need a vba code that will look in the 2nd row for a specific value and return that column's letter. For example, searching the row for 7 would return "E".

How can I do this?

[Marcol]

Assuming that you have "Headers" in Row 1

This will return what you want

Please Login or Register  to view this content.

where H2 has the value you are looking up

If you have no header

Please Login or Register  to view this content.

where H2 has the value you are looking up


Hope this helps

[Richard Buttrey]

Hi,

You don't actually need VBA for this you could use standard Excel functions.

If the result will always be between columns A-Z then:

Please Login or Register  to view this content.

otherwise

Please Login or Register  to view this content.

it might be advisable to use a variable cell to hold the number 7, or whatever and substitute the cell address for '7' in the formula.

Rgds

[zapp7]

Thanks for the responses. The columns do go past Z, all the way to AP. This is part of a much larger macro. Essentially, the user inputs an even integer (ex. 40) and then I need to search for that number in a row. Once I have the row, I need to make a chart with an array of data starting in that column. This is what I have so far:

Please Login or Register  to view this content.

First off, the L1 cell that is searching for the formula ends up showing just a 0 instead of a letter.

It feels like there should be an easier way to do this in the code rather than putting a formula into a cell and extracting that cell's value.