Macro to drag formula and paste values

[ricdamiani]

EDIT 3rd AUGUST:
Bumping up.

In a nutshell:

1) the row number 3 would have the formula column (A3:BU3)
2) macro would drag the formula down up to row 1000 (columns "A3:BU3" from row 4 to 1000)
3) macro would copy and past values from row 4 to 1000 (columns "A3:BU3"), keeping original formula in row 3

Thank you!!



Hi guys,

Once again need your expertise, any help would be appreciated.

The formula below is slowing down the spreadsheet and looking for a macro that could:

1) the only cell that would keep the formula would be the first cell in the row 2 (column "i")
2) macro would drag the formula down up to row 1000 (column "i" from row 3 to 1000)
3) macro would copy and past values from row 3 to 1000 (column "i"), keeping original formula on row 2

Please Login or Register  to view this content.

That would fix the issue.

Thanks everyone,

Rick

[AlphaFrog]

Please Login or Register  to view this content.

[Trebor76]

Hi ricdamiani,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

[ricdamiani]

Please Login or Register  to view this content.

Hi ricdamiani,

Try this:

Please Login or Register  to view this content.

Regards,

Robert

Hi Robert and AlphaFrog,

Both macros are working, thank you very much!

Regards,

Rick

[ricdamiani]

Hi guys,

The spreadsheet has changed a bit, and instead of auto filling only column I (auto fill from cell I3 to I1000, based on the formula in I2... after copying and pasting values of I3 to I1000, as per macro above), it needs now to be all cells on row 3, from column A to BU (so that's A3:BU3), autofill up to row 1000, and copy and paste values from row 4 to row 1000.

Tried to do my self but didn't work, would appreciate if you guys could help me on that.

Thanks,

Rick

[FDibbins]

=IFERROR((
VLOOKUP(A2,Sales!$B:$AR,MATCH(CONCATENATE($O$1,"/",$P$1),Sales!$3:$3,0)-1,0)+
VLOOKUP(A2,Sales!$B:$AR,MATCH(CONCATENATE($O$1,"/",$P$1),Sales!$3:$3,0)-2,0)+
VLOOKUP(A2,Sales!$B:$AR,MATCH(CONCATENATE($O$1,"/",$P$1),Sales!$3:$3,0)-3,0)+
VLOOKUP(A2,Sales!$B:$AR,MATCH(CONCATENATE($O$1,"/",$P$1),Sales!$3:$3,0)-4,0))/4+(
SUMIFS(Blending!$E:$E,Blending!$A:$A,A2,Blending!$J:$J,$Q$1)*-1+
SUMIFS(Blending!$E:$E,Blending!$A:$A,A2,Blending!$J:$J,$R$1)*-1+
SUMIFS(Blending!$E:$E,Blending!$A:$A,A2,Blending!$J:$J,$S$1)*-1+
SUMIFS(Blending!$E:$E,Blending!$A:$A,A2,Blending!$J:$J,$T$1)*-1)/4,"")
1. Instead of a macro, try adjusting those ranges - instead of full-column ranges, use only the range you need
2. use helper columns to do some of the heavy lifting/repeated functions
Instead of all those concat's (at least 4?) put this in a helper cell...=$O$1&"/"&$P$1 then just reference that cell
in fact, that whole section...MATCH(CONCATENATE($O$1,"/",$P$1),Sales!$3:$3,0) can be put in 1 cell and referenced

[ricdamiani]

=FDibbins;4447211
1. Instead of a macro, try adjusting those ranges

That's a good idea FDibbins, thanks for that, but actually as you can see in my previous post the spreadsheet has changed and now I have a lot of columns (A to BU) and macro would be the only solution.

But I will do adjust my formula with your tips.

Hope someone can help me with an amended macro.

Thanks,

Rick

[ricdamiani]

Bumping up.

In a nutshell:

1) the row number 3 would have the formula column (A3:BU3)
2) macro would drag the formula down up to row 1000 (columns "A3:BU3" from row 4 to 1000)
3) macro would copy and past values from row 4 to 1000 (columns "A3:BU3"), keeping original formula in row 3

Thanks

Rick

[ricdamiani]

Bumping up, please help.

[ricdamiani]

Hi guys,

Anyone there that could guide me?

Thanks,

Rick

[AlphaFrog]

Bumping up.

In a nutshell:

1) the row number 3 would have the formula column (A3:BU3)
2) macro would drag the formula down up to row 1000 (columns "A3:BU3" from row 4 to 1000)
3) macro would copy and past values from row 4 to 1000 (columns "A3:BU3"), keeping original formula in row 3

Thanks

Rick

Please Login or Register  to view this content.

[ricdamiani]

Thanks AlphaFrog, it's working.

[FDibbins]

Please wait at least a day before bumping your thread