Link to home
Start Free TrialLog in
Avatar of activematx
activematxFlag for United States of America

asked on

Excel Macro to copy a formula down cells

Hi Excel Experts,

Take a look at this screenshot:  User generated image
I've already entered the formula that I would like copied down the cells into the cells which are highlighted green.  I did this manually.  I would like the macro to do this for me all the way down the spreadsheet.

I've highlighted the next cells where the formula should go yellow, so you can get the idea.

Can someone help me with this macro?

I have attached the original workbook.  Thanks!

I have also attached my workbook with macros which experts helped me with earlier. That might be useful for you to see what they did.  Otherwise, it's not necessary to open the macro workbook if you are really good with macros.
FY12-Invoices-Need-Macro.xlsx
FY12-Invoices-Need-Macro.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of activematx

ASKER

Hi ssaqibh!

Thanks, this macro does exactly what i want it to do.  The formula is pretty complex though, compared to my original.

Was there a reason this formula had to be used instead?

I am going to award you the points for this answer, but would you mind briefly explaining to me what the formula in the cells does:

=IF($L32=0,"",SUMPRODUCT(($F$2:$F32=$F32)*$I$2:$I32*$M$2:$M32)/SUMPRODUCT(($F$2:$F32=$F32)*$M$2:$M32))
This formula is essentially
=SUMPRODUCT(($F$2:$F32=$F32)*$I$2:$I32*$M$2:$M32)/SUMPRODUCT(($F$2:$F32=$F32)*$M$2:$M32)

The first sumproduct  sums the results of
($F$2=$F32)*I2*M2
($F$3=$F32)*I3*M3
($F$4=$F32)*I4*M4
($F$5=$F32)*I5*M5
($F$6=$F32)*I6*M6
.
.

The terms ($F$2=$F32) etc return 1 if the two terms are equal and 0 if not. In effect it is the sum of
I2*M2
I3*M3
I4*M4
I5*M5
I6*M6
.
.

whenever the corresponding F column value is equal to F32

Similarly the second sumproduct sums up
($F$2=$F32)*M2
($F$3=$F32)*M3
($F$4=$F32)*M4
($F$5=$F32)*M5
($F$6=$F32)*M6