VBA - Copy and paste loop, with results

[Giuseppeb90]

I have a table with many values (lots of rows) and I have to copy values from each row (highlited in yellow) in some specified cells. Each of those rows will give me a result which I have to copy in another cell. Once this process is done i have to repeat it for each row of the table.

The example is attached as image, numbers highlighted (1) in yellow have to be copied (2) and the number in the blue circle (3) will be the result to be paste (4)!

How can I do it with VBA?.

thanks in advance!

 

[Giuseppeb90]

Thanks for more information.

The final formula is:

=MAX(MAX(E36:J36),ABS(MIN(E36:J36)))/N8

(Which I believe the equivalent of the following shorter version)

=MAX(ABS(E36:J36))/N8

In both cases, we need E36:J36 values. Those are all same formulas, total of 5 cells above. E36 is:

=+SUM(E31:E35)

What we need to do is creating AF7 formula once, and copy it down.
See the following for AF7 cell formula please. All I did was combining E36, F36, G36, H36, I36, and J36 cells and get the MAX of ABS of the calculations then divide by N$8.

=MAX(
 ABS(1000*X7/D$21 + 0 + 0 - (10^6*AA7/I$21) - (10^6*AB7/K$21)),
 ABS(1000*X7/D$21 + 0 + 0 + (10^6*AA7/I$21) - (10^6*AB7/K$21)),
 ABS(1000*X7/D$21 + 0 + 0 - (10^6*AA7/J$21) + 0),
 ABS(1000*X7/D$21 + 0 + 0 + (10^6*AA7/I$21)),
 ABS(1000*X7/D$21 + 0 + 0 - (10^6*AA7/I$21) + (10^6*AB7/K$21)),
 ABS(1000*X7/D$21 + 0 + 0 + (10^6*AA7/I$21) + (10^6*AB7/K$21))
)/N$8

I am not entirely sure about the consistency of the formulas in the worksheet. For example, why G34 is using J21 while the other adjacent cell formulas are using I21, but I am sure there is a reason for that. Apart from the formula structure and the calculation logic, the final values for the given values in X:AB range could be calculated by using formulas only instead of using VBA.

Note for your future questions: If you could have sent an XL2BB mini-sheet instead of a screenshot, then your formula could have been more accurate by using the actual formulas instead of trying to replicate the worksheet structure at this side, but this should give you the idea.

Thanks! I didn't realise I could join the cells all together, this is very useful

I will install XL2BB the next time

 

[smozgur]

Thanks! I didn't realise I could join the cells all together, this is very useful

I will install XL2BB the next time

You're welcome. Glad to hear it helps.

One final note. If you received an answer that solves your question on the board, then please mark that post as the solution to help future readers who might be looking for this solution.