VBA - Copy and paste loop, with results

Giuseppeb90

New Member
Joined
Apr 9, 2024
Messages
6
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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!
 

Attachments

  • excel.jpg
    excel.jpg
    146.8 KB · Views: 23

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
What is the relationship and criteria between the yellow line on the right and the yellow line on the left?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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!


VBA Code:
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
 

Attachments

  • excel.jpg
    excel.jpg
    255.7 KB · Views: 24
Upvote 0
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
 

Attachments

  • excel.jpg
    excel.jpg
    254.9 KB · Views: 22
Upvote 0
Thanks for more information.

The final formula is:
Excel Formula:
=MAX(MAX(E36:J36),ABS(MIN(E36:J36)))/N8
(Which I believe the equivalent of the following shorter version)
Excel Formula:
=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:
Excel Formula:
=+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.

Excel Formula:
=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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top