PDA

View Full Version : Quicker way to write a bunch of formulas??



nathan2314
07-01-2008, 07:31 AM
Good Morning All :hi:
I'm building a GUI in excel and am having to manually write a bunch of long formulas for alot of different cells (would end up being about 7000 total..maybe even more later if my customers want more capability). Each of the formulas are related though as all I'm having to do is increment a piece for each cell. Here is an example of the formula.

=SUMPRODUCT(--(MONTH('RFF Data 179'!$G$2:$G$500)=1),--(YEAR('RFF Data 179'!$G$2:$G$500)=2008),--('RFF Data 179'!$D$2:$D$500="O3"),--('RFF Data 179'!$H$2:$H$500=6),'RFF Data 179'!$F$2:$F$500)

The month() piece at the begining will have to go 1-12, the year() will be a range from 2008-2009, the "O3" part will go from O2-O6 and E1-E6, and the last test piece which has a '6' will go 1-12, and then on a different sheet I will have the exact same data slices except now the "RFF Data 179" will be changed to "RFF Data 365". So thats 12*2*11*12*2 (6336) different formulas I would have to write for 6336 different cells. There has got to be an easier way. Maybe a macro that I could write where it could go in and change those pieces automatically? I'm not sure how to access manipulate a cells formula from within a macro though?? Any suggestions??

Appreciate any Help!! :bow:

nathan2314
07-01-2008, 08:14 AM
I figured it out by using the .replace in vba
With Selection
.Replace What:="RFF Data 179", Replacement:="RFF Data 365"
End With

pretty easy actually once I knew what to do..
Ahh knowledge is truly power...that just saved me hours of work :rotlaugh: