Creating variables based on cell location

**length** · 10-10-2009, 05:15 PM

Hi all,

A novice VBA programmer here! I am attempting to write a macro which is essentially populating an Excel 2007 spreadsheet through stochastic simulation. I need to log the number of times a cell is activated, under each of four contexts.

So essentially, if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1.

The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!

Thanks very much in anticipation of your help!

**Shijesh Kumar** · 10-10-2009, 05:30 PM

Modify the code as per ur need

Please Login or Register  to view this content.

**shg** · 10-10-2009, 06:54 PM

Shijesh,

In Excel 2003, that would be a 33MB array. In Excel 2007, which the OP is using, it would be 34GB array.

Length, another way would be to use a separate sheet:

Please Login or Register  to view this content.

**length** · 10-11-2009, 01:10 PM

Thanks guys,

Four additional sheets (one per count variable) is a pretty neat solution which would certainly work - and probably be the better option for spreadsheets requiring thousands of rows and columns...For this purpose, I've modified the array code suggested by Shijesh (as I do not need every available cell so size isn't an issue).

Cheers