Find heading, the subheading, display value

Hi,

I have a spreadsheet (excel 2000) and it has headings and subheadings in the same column, with values in the next column, as attached.

What I need is to perform a lookup or search that recognises the heading, and finds the correct subheading, and displays the value next to that subheading. i.e.:

If I want "BBB" heading, and "ghi" subheading, the value displayed would be 12.

I have been trying to use the find function to find the heading, but cannot find code to search the Subheading:
Cells.Find("BBB", , xlValues).Activate

I have been rattling my brain trying to find any formula or code to perform this, any help would be much appreciated.

Thank you in-advance.
Matt

I don't know if this is any use:

Please Login or Register  to view this content.

Hi TJ,

This is almost exaclty what I am after!! Except instead of a user inputing the figures via a txtbox, can the code link to cells on the spreadsheet that automatically configure the search and then source the outcome value into another cell.

What you have already done is great, and I am sure I can figure out what I am after from this but any other help would be great.

Cheers again, you're a legend!!
Matt

Instead of the inputboxes use -

strHeading = Sheets(1).Range("c1").Value
strSubHeading = Sheets(1).Range("d1").Value

and to write the value to the sheet -

Sheets(1).Range("e1").Value = rngSubheading.Offset(, 1).Value

Change the sheet and cell references as required.

Thank you again for all the help, I have been spending ages trying to find this code and now it works like a dream!!!!

Cheers,
Matt

Guys,
Sorry to jump in on a very old thread. Can anybody tell me a simple way how to convert above mentioned subroutine to a function. I wanna use a custom function instead of subroutine as I want to use this function directly in sheet instead of calling a subroutine time and again.
Any response is highly appreciated.

Thanks madam. I will surely post a new thread with link to the current thread.