Changing Manually entered matrix values via macro

This problem doesn't seem to be that hard to do, but I'm having a hard time doing it efficiently without hardcoding all the cell locations I'll be changing.

The basic idea is that I have a matrix (array) from b2:f9, which are all manually entered values. If certain conditions are met, I want to subtract 1 from whatever value is currently in the cell. That part I've already figured out.

I've got up to 4 cells I have to clear at a time, and I'm having a hard time referencing them correctly. I need something similar to the index and match functions in excel that I can use in VB and I could do it no problem.

I have the names of the row and column descriptors saved so I can find the intersections and change the data that way, but I'm not sure the easiest way to go about it.

For example, if cell V12 is true, I wish to decrement each of the values in the manually entered table that made it true.

This is what I have right now:

Please Login or Register  to view this content.

I can already tell this isn't going to be very useful to me as all I'm doing is hardcoding the cell references. I need a way to use the data i have stored in the sheet itself to find out the spots that need decrementing.

The row and column headers are stored in AA12:AB36, where AA contains cells which have the row headers, and AB contains cells which have the column headers. There are 3 more sets in the proceeding columns so that AC12 & AD12 make another set, AE12 & AF12 make another, etc.

If ALL 4 of the sets in a row have value > 0 in the manually entered table then ALL 4 of the cells in the table need to be decremented by 1.

Sorry for being clear as mud but I'm trying to be as clear and concise as I can while being descriptive.

Welcome to the forum.

I need something similar to the index and match functions in excel that I can use in VB and I could do it no problem.

Then you are all set: both INDEX and Match are available to VBA:

Please Login or Register  to view this content.

Well thats just awesome then. Is every function in excel available via the WorksheetFunction.* ?

See VBE Help for worksheet functions