Define data range in excel and search in a cell content using VBA

[WaelYassin]

I am using Excel 2003. i have work sheet consists of
A B C D
xaxaax 3 DDDD 3

4 columns as you can see, column A is header and B is values and the same for C & D.

what i want is to search for specific header or cell at A or C and then retrieve the value corresponding in B or D.

this will be exercise to fill data from several sheet in one master sheet.

Thanks for your time

 

[SkipVought]

Hi,

This can very simply be accomplished using the INDEX() and MATCH() functions. Foe instance...
[tt]
=INDEX(B:B,Match("xaxaax",a:a,0),1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WaelYassin]

Thanks Skip for your help,
But i didn't understand the line even from the F1 Help, how it work?

second thing is that the purpose is to iterate on group of excel files and extract their similar template data into designated master excel file. i think it should be some kind of Macro programming to achieve this.
the template for the similar files consists of 4 colums the first and for headers and the second is value of the header.

i need to open the file and take the first column and iterate on the headers, searching each text in the master sheet and then copy the value of the header under the master sheet header corresponding.
i hope it is clear.

 

[SkipVought]

All you need is a unique list of the header values. Not need for a macro to do this.

Then you reference the header value in the formula I posted instead of a literal, and copy/paste that formula thru the rows or columns of your list.

ALL the above could be done in less than 60 seconds.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]