VBA match, index, lookup... confused

**strixy** · 08-15-2007, 04:00 PM

Hi there,

I'm just looking at VBA for the first time. I'm coming from a PHP, BASH and assorted other Linux languages background. Short of throwing this Excel spreadsheet into a MySQL DB and doing what I want on the server in PHP, I thought I would poke around with this VBA stuff and maybe learn something new.

I have a rather large spreadsheet that is based on about 12 worksheets (9k+ rows) and it's been a blast learning how to tie them in to one another with Excel functions.

I have to find a way to search row 1 (in one of those worksheets and then return all the data in that row.

I thought I would insert an Excel 5.0 dialog. Create an input form to match the input to the worksheet then return the row info in a nicely formatted page.

I see there is some scripting capability here, but I'm not sure where to start.

I've done some searching over the past two days, including this forum, and some people have mentioned a match function, but I'm not sure where the resources on VBA are. I went to the VBA Excel document site at msdn.microsoft.com, but I can't drill that thing for anything.

If anyone has an example I could work off of or a couple of URL's to documentation that would be appreciated! There doesn't seem to be a whole heap of people out there who cover this kind of stuff.

I've got about a three days to put this together or I'll have to drop the project and go with what I know.

(Sorry about the double post. My bad.)

**rylo** · 08-15-2007, 09:16 PM

Hi

How would you like to identify the sheet that has the data you want? Do you want to nominate the sheet, or some info that may be contained in the row????

rylo

**strixy** · 08-16-2007, 06:26 PM

I set up the format I was looking for in a second worksheet. The code I used for one of the cells is shown below.

C4 is the cell where you enter the p/n you want to find.

=MATCH(C4,Sheet1!A3:A740,0)+2
=INDEX(Sheet1!$A3:$T740,$A6,1)

The first line is in cell A6. It finds the part number in sheet1 (as entered into cell C4) and returns the row number.

I used the second line to find the row for the p/n (as returned by the first line). Then display the items by placing the column number for whichever item I need to display as the last number in formula on the second line.

It works well in the spreadsheet, and people are thrilled! I just thought I would learn how to do the same thing in VB for the experience. I think it would look even slicker that way.