Opv
03-24-2010, 09:16 AM
I've been working with the formula property to assign formulas to particular cells. I got to wondering whether it is possible to to do some stuff on the fly within VBA without actually populating a cell with the formula.
By that, I mean, is it possible to: 1) construct a formula (the specific formula below); 2) calculate the resulting value; 3) assign that value to a variable, without populating a cell; and then to 4) use that Value in yet a second formula that will be used to populate a cell.
=IF(WEEKDAY(DATE(YEAR(TODAY()),1,1),1) =1,DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)+ (8-WEEKDAY(DATE(YEAR(TODAY()),1,1))))
For example, this formula calculates the first Sunday of the current year. What I'm wanting to do is calculate this date without populating a cell on my worksheet, then assign the resulting date value to a variable which can then be referenced in another formula.
Possible?
By that, I mean, is it possible to: 1) construct a formula (the specific formula below); 2) calculate the resulting value; 3) assign that value to a variable, without populating a cell; and then to 4) use that Value in yet a second formula that will be used to populate a cell.
=IF(WEEKDAY(DATE(YEAR(TODAY()),1,1),1) =1,DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)+ (8-WEEKDAY(DATE(YEAR(TODAY()),1,1))))
For example, this formula calculates the first Sunday of the current year. What I'm wanting to do is calculate this date without populating a cell on my worksheet, then assign the resulting date value to a variable which can then be referenced in another formula.
Possible?