Excel sheet with too many formulas - to VBA

[mar050703]

Please can someone help me.

I am only self taught in this VBA stuff. I have a spreadsheet which has a sheet name for each of the months of the year, each sheet has a formula for each day fo the year (and for each employee (66ish). I have built a simple VBA program that will allow me to add data to a sheet called Dates. Each of the formulas (on the months sheet) read from this sheet.

The formula is:

=IF(SUMPRODUCT(($B3=Sname)*(Sfrom<=C$2)*(Sto>=+C$2))+IF(WEEKDAY(C$2,2)>5,2,0)=1,"A","")

Is there a way to cause the VBA program when it enters the data into the Dates sheet to action this formula. Therefore not needing the formula 24,000 times.

I hope this makes sense.

If it the last four days of the month (I have a slightly different formula

=IF(SUMPRODUCT(($B3=Sname)*(Sfrom<=AD$2)*(Sto>=+AD$2))+IF(WEEKDAY(AD$2,2)>5,2,0)=1,"A","")

Can anyone help or make sense of this

 

[SkipVought]

Hi,

Not exactly sure what you mean.

When you enter a formula you can COPY/PASTE into the other 23,999 rows.

Why do you have a DIFFERENT formula for the last 4 days of the month?

Lastly, this statement "I have a spreadsheet which has a sheet name for each of the months of the year" is a RED FLAG, as it will greatly multiply your sorrows and woes. Chopping data up into different locations (sheets) is a BAD data storage design. It renders your data difficult to maintain, analyze, and report. Data of a similar type ought to reside in ONE TABLE. If necessary, it can be REPORTED into separate sheets. I ususally even avoid that, preferring to use simple controls like the AutoFilter or PivotTables to select slices of data.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[mar050703]

Um.

I wanted one sheet, but there are no enough columns for each of the 365 days of the year.

I have copied the formula into all the relevant cells, hence why the workbook is slow.

What I wanted was to somehow use that formula (or something like) within VBA. Basically I want a letter 'A' to appear on the selected dates whenever these dates appear on the 'Dates' sheet, but they must appear against the employee for that month.

I am guessing in VBA I would not need the different formula for the last four rows of each sheet (Jan to Dec).

Does that help??

 

[SkipVought]

"I wanted one sheet, but there are no enough columns for each of the 365 days of the year."

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Dates, as DATA ought not to be COLUMN HEADINGS. That's for a REPORT, not for data STORAGE.

Please post a sample of your current data layout.







Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[mar050703]

I can not see how to upload the file?

 

[SkipVought]

Try this...

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}