Using a MATCH in VBA is driving me crazy!

[zookeepertx]

I have been trying to use a MATCH function as part of my macro and can NOT get the syntax right. Or else I've created a bigger issue; I'm not sure which. I'll attach a workbook - The first page is how I start out and the second page is how I need to end up. I've got the 4 rows inserting at the top and got them formatting correctly. I need to ultimately have a SUMIF fill the cells in row 3 with the results summing column J. (Apparel and GOH are combined and Flat and Security Mark are combined), but I THINK that, in order to do that, I need to specify the end of each section, to tell the SUMIF where to stop looking, right? That's where I'm running into the problem.

I'm trying to use MATCH to get the row # of the end of each of the sections: Apparel, Cosmetic, etc. If I put a MATCH formula in a cell on the worksheet it works fine. I've tried to translate it into VBA, but am failing miserably. Here is my current, non-working code:

Please Login or Register  to view this content.

( I just thought I'd try to get it to identify the first 2 sections before carrying on to the rest of them).
The problem comes at the 2 red lines. I've tried changing the syntax to EVERY option I can imagine and, every time I change it, I get a different error message! When I F8 through the current code gives a 2042 error on both of those lines.

I've been googling for probably 2 hrs and am no closer to an answer.

Can anyone see the (probably simple) error I'm making?

Thanks!

Jenny

[sktneer]

Try this.....

Please Login or Register  to view this content.

Does this help?

[zookeepertx]

Huh - I thought I had tried that, but I've made so many changes that I forget. That does work - YAY!

Here's a weird thing, though. If I just type the MATCH formula into a cell in the spreadsheet, it finds the row # of the SECOND instance of the word (Apparel, in the first case). (Actually, it finds how many rows below row 5 that last instance is). But in the macro, it finds the FIRST instance of the word (also how many rows below row 5 that first instance is). I actually need the SECOND instance.

Also, I guess I need to start at A1 instead of A5, so it gives the correct row number. But since I've inserted a row that also says APPAREL, I actually need to find the third instance, only in the case of APPAREL. But the second instance of each of the other words. Do you know how to do that?

Have I been totally confusing? I hope not.

Thank you for the progress so far!

Jenny