How to find the location (cell position) of a value in a row via Excel VBA

Stanley Lai
Stanley Lai used Ask the Experts™
on
Hi,

I'm new to Excel VBA.  And now I wish to write some code using Excel VBA to search the cell position on a specific row (say row 1, not column A).

For example, row 1 contains some currency code in some uncertain positions.  If I provide a currency code, say USD, and the currency code USD is located on the row 1 column Z (e.g. cell Z1).  And the search can return me either 26 or Z.

How can I do this using VBA code?  Please help.

Stanley
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

Open in new window


ActiveSheet works in current worksheet. You can use also sheet name instead of, e.g. Sheets("MySheet"), rest code is the same.

Author

Commented:
Hi,

But how I provide my search argument?

Row 1 contains cells A1, B1, C1,D1 .... the value that I seek for its position may be in any of the cells in row 1.  If my lookup value is in A2, then how I write the code?

Stanley
Most Valuable Expert 2011
Top Expert 2011
Commented:
Something like:
Dim varCol
varCol = application.match("USD", activesheet.range("1:1"), 0)
if not iserror(varcol) then msgbox "Column number is " & varcol

Open in new window

Author

Commented:
Thx a lot.  ^^

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial