For each row, insert a formula that multiplies with a specific cell

Hi all,

I have created a macro that provides me with a data overview, rather helpful.

However, for each row I would have to add a formula in a column which multiplies a value in that row with a header value.

Let me explain by the attached example.

Explanation of attached file:

I get a "header" name in cell B2 and every 22th row below (for 100,000 rows), thus row 2 + (i * 22).
I get a "header" value in cell C2 and every 22th row below (for 100,000 rows), thus row 2 + (i * 22).
Below each name&value are a maximum of 20 rows with data for each name, but it can be less as well. There can even be no data, but then the starting cell (B3 and row 3 + (i * 22)) would show something like "no data available".

Question:

Would it be possible to build a VBA code in a separate sub() module that puts a formula (or the value, if the VBA can calculate the value) in column L for each row with data, that multiplies the value in column E with the "header" value?

I have included the formula for the first 2 sets of data. For B3:K22 the formula refers to C2 value, for B25:K31 the formula refers to C24 and this should continue in a similar fashion.

Thinking out loud:

It might be less stressful for excel to have VBA calculate the value (of multiplying value in the E column and respective row with the "header" value) than it is to insert the formula in column L instead? Because in the latter case, it would have to insert a formula and excel would have to execute the formula after VBA has run its course?

Hope someone can help with this matter.

Thank you in advance.

Maybe something like this ?

Please Login or Register  to view this content.

Je zou dit kunnen proberen.

Please Login or Register  to view this content.

Without looping, which should be faster:

Please Login or Register  to view this content.

Originally Posted by jolivanes

Without looping, which should be faster:

Please Login or Register  to view this content.

That is rather impressive. I have not yet worked with specialcells and Areas yet, so I am now doing some google searches before I understand. ;-)

But it looks very good and even more important, it works great!

Thank you for your help. I will add the earned reputation

Originally Posted by karmapala

Maybe something like this ?

Please Login or Register  to view this content.

Hi karmapala,

This is a bit easier to read for me than jolivanes code and it works great as well!

Thank you for your help as well. I will also add reputation.

@karmapala, jolivanes, would either one of these codes be easier (thus faster) for excel?

Glad it help you, and thanks for the rep, Excel-VBA.

I myself is not experience in vba, and to be honest ...
I'm not familiar using a column/row number reference like jolivanes code.
So, about "easier (thus faster)" code, I'm sorry I don't know which one.

Put this just before "Application.ScreenUpdating = False"

Please Login or Register  to view this content.

and this just after "Application.ScreenUpdating = True"

Please Login or Register  to view this content.

in both suggestions by me (Post #3 and Post #4)
If you have a very large file, you should see a slight difference in time.
The 2nd suggestion should be the fastest one because it does not loop the cells in each block (area) of cells.

I don't know if your original file is different from the attached workbook in the first Post but karmapala's code jumps 22 rows for the multiplier cell and that is not the case in your attached file.
However, that's what you mentioned in your first Post.

Thanks for the rep.

I don't know how large your data range is but when I copied/pasted the data you supplied several times to where the last used cell is in Row 1320, I get 8/100th sec for the first macro and 5/100th sec for the second macro.
If you have a considerable larger range, it would be interesting to see what the actual differences are.