activematx

asked on

# Excel Macro to copy a formula down cells

Hi Excel Experts,

Take a look at this screenshot:

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

Take a look at this screenshot:

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

=SUMPRODUCT(($F$2:$F32=$F3

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

ASKER

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((