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!

 

[smozgur]

Welcome to the MrExcel Message Board!

Do you absolutely need to use VBA for some reason?

Because, I think it might not be necessary to use VBA to do what you need. When you copy the cells from section (1), the calculation in (3) should be already using these values and likely some other values in the other cells at the left side of the sheet. You can simply build a single formula that will use the cell references for 5 cells in (1) without absolute cell references, and other cells from the left section with absolute references. This way, you can copy the formula that you build in the first cell of section (4) down and get the calculated result for each row.

If you could let us know the formula in cell (3), and ideally show the row and column headers, I believe it could be solved without VBA.

 

[akmatz]

What is the relationship and criteria between the yellow line on the right and the yellow line on the left?

 

[Giuseppeb90]

Welcome to the MrExcel Message Board!

Do you absolutely need to use VBA for some reason?

Because, I think it might not be necessary to use VBA to do what you need. When you copy the cells from section (1), the calculation in (3) should be already using these values and likely some other values in the other cells at the left side of the sheet. You can simply build a single formula that will use the cell references for 5 cells in (1) without absolute cell references, and other cells from the left section with absolute references. This way, you can copy the formula that you build in the first cell of section (4) down and get the calculated result for each row.

If you could let us know the formula in cell (3), and ideally show the row and column headers, I believe it could be solved without VBA.

Hi, thanks for your answer, I need to use VBA because I have to copy and paste more than 1000 rows, and for each row copied to point "2", the sheet does a calculation which the output is the point "3".....if I copy a new series of values to point 2 the output "3" will change and so on

 

[Giuseppeb90]

What is the relationship and criteria between the yellow line on the right and the yellow line on the left?

the yellow line on the right is part of a table of results which a software gives me, the yellow line on the left is where I put each row to get a result through a calculation

 

[smozgur]

I still don't see any necessity of using VBA, and do believe it could be used by using formulas only. When you copy (1) to (2), a new (3) is calculated and you want it to be copied to (4) for that row. As I said, your mystery formula in (3) is necessary to be revealed to provide more help, and perhaps see the necessity of VBA.

However, here is the code for a simple VBA way. Since we have no idea about your data structure (columns / rows), this might be just a guide. Hope it helps.

Sub doIt()
Dim rng As Range
Dim cll As Range

    ' This is section (1) range. Need to change it with actual range
    Set rng = Range("K3:O16")

    For Each cll In rng.Rows
        ' Copy (1) to (2)
        Range("A23:E23").Cells.Value = cll.Value
        ' Copy (3) to (4)
        cll.Offset(, 8).Resize(, 1).Value = Range("G23").Value
    Next cll
End Sub

 

[Giuseppeb90]

I still don't see any necessity of using VBA, and do believe it could be used by using formulas only. When you copy (1) to (2), a new (3) is calculated and you want it to be copied to (4) for that row. As I said, your mystery formula in (3) is necessary to be revealed to provide more help, and perhaps see the necessity of VBA.

However, here is the code for a simple VBA way. Since we have no idea about your data structure (columns / rows), this might be just a guide. Hope it helps.

Sub doIt()
Dim rng As Range
Dim cll As Range

    ' This is section (1) range. Need to change it with actual range
    Set rng = Range("K3:O16")

    For Each cll In rng.Rows
        ' Copy (1) to (2)
        Range("A23:E23").Cells.Value = cll.Value
        ' Copy (3) to (4)
        cll.Offset(, 8).Resize(, 1).Value = Range("G23").Value
    Next cll
End Sub

Hi! thanks this is perfect, I will attach a screenshot fo the overall sheet.
I could not do it manually because there are 4000+ rows to copy, and I don't know any way using a normal formula to do it
If there is a formula which can help me, I would like to know more about it!

Sub doIt()
Dim rng As Range
Dim cll As Range

    ' This is section (1) range. Need to change it with actual range
    Set rng = Range("X7:AB4000")

    For Each cll In rng.Rows
        ' Copy (1) to (2)
        Range("D26:H26").Cells.Value = cll.Value
        ' Copy (3) to (4)
        cll.Offset(, 8).Resize(, 1).Value = Range("J26").Value
    Next cll
End Sub

 

[smozgur]

Hi! thanks this is perfect.

You're welcome.

If there is a formula which can help me, I would like to know more about it!

I will ask again: Please let us know the formula in (3) - cell J26. This way, we might be able to provide a formula solution instead.

 

[Giuseppeb90]

You're welcome.


I will ask again: Please let us know the formula in (3) - cell J26. This way, we might be able to provide a formula solution instead.

There you go!
basically J26 takes the min/max abs value from row E36:J36, which is function of the values added in row D26:H26

 

[smozgur]

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.