using a formula within a macro

[jimb0693]

dont know if this is even possible but i would like to use a formula within a macro and have it run every time the workbook is opened. the formula would do the following

if(e21+56>=an21)then delete contents of cell f21

at the moment i have to check through and delete these manually for 700 rows every week, quite time consuming as you can imagine so if i could automate it, it would save me a lot of hassle

any ideas greatly appreciated

thanks

[Paul]

Hello jimb,

Are you wanting this calculation to occur on hundreds of rows at once (e.g. rows 1:700), or only on row 21 when you open the file?

[jimb0693]

on all rows in sheet which can vary week to week

[JBeaucaire]

This is a simple macro for the test, run it on-demand when you open your sheet.

Please Login or Register  to view this content.

I'm checking column E for the last value/last row. You can change that to any column you wish.

[Paul]

Note: Because you're not deleting or moving rows during the macro, you don't need to use

Please Login or Register  to view this content.

You could simply use

Please Login or Register  to view this content.

When deleting rows, it's best to go in reverse so as to avoid skipping rows as the value of i increases yet the number of rows decreases.

[JBeaucaire]

Yeah, I read the original question wrong and when i fixed it, forget to reset the direction. Rookie error, though the macro will still work ... backwards!

[jimb0693]

thanks for the help but i tried this and am getting runtime error type mismatch with the following highlighted any ideas?

If Cells(i, "E").Value + 112 = Cells(i, "AN").Value Then _

i cant see anything wrong

by the way my original count was wrong so i changed 56 to 112 ( number of days in 16 weeks) this wouldnt have made a difference would it?

[JBeaucaire]

It could make a difference. Math is math, so that's not the problem, the issue would be the sheet or the data, I imagine.

Click on GO ADVANCED and use the paperclip icon to post up your misbehaving sheet and I will look it over.