See attached.

Copy-of-Copy-values-in-ranges.xlsx

Solved

Posted on 2011-10-19

Hello Experts,

I need a bit of code that will increment through several ranges, copy and drop the values into another range where a calculation will be performed, then drop the results back into another incremented range. Speed is key, lots of ranges to calculate.

See the attached sheet.

Thanks,

biker9

Copy-values-in-ranges.xlsx

I need a bit of code that will increment through several ranges, copy and drop the values into another range where a calculation will be performed, then drop the results back into another incremented range. Speed is key, lots of ranges to calculate.

See the attached sheet.

Thanks,

biker9

Copy-values-in-ranges.xlsx

12 Comments

See attached.

Copy-of-Copy-values-in-ranges.xlsx

```
Sub ProcesssBlocks()
Dim rngSource As Excel.Range
Dim rngTarget As Excel.Range
Dim rngPreCalc As Excel.Range
Dim rngCalculated As Excel.Range
Dim i As Long
' define no of rows and cols here, so it only neds to be changed once
Const lngRows As Long = 6, lngCols As Long = 3
' set data-dependent locations
Set rngSource = ActiveSheet.Range("C5").Resize(lngRows, lngCols)
Set rngTarget = rngSource.Offset(19, 0)
' again the location of these will probably be different in your solution
Set rngPreCalc = ActiveSheet.Range("P5").Resize(lngRows, lngCols)
Set rngCalculated = rngPreCalc.Offset(9, 0)
For i = 1 To 3 ' this is dependent on your data
rngPreCalc.Value = rngSource.Value ' copy source to staging area
rngTarget.Value = rngCalculated.Value ' copy calculated figures to final area
' move to the next source and target locations (offset dependent on layout)
Set rngSource = rngSource.Offset(0, 4)
Set rngTarget = rngTarget.Offset(0, 4)
Next i
End Sub
'
```

So Andrew, your solution works great, but my result ranges are a different size then the input range (sorry should have mentioned that at the outset), so if you could adjust this for me I would much appreciate. The calculated result and the output range will always be the same size.

Tx,

Ray

Copy-values-in-ranges-Rev.xlsx

In fact you can even copy a hundred sets of formulas in one go. You do not have to copy one set at a time. You only have to make sure that the input data are pasted at a regular interval and not at a random spacing horizontally.

Copy-values-in-ranges-Rev-2.xlsx

Copy-values-in-ranges-Rev.xlsm

For a sensible, scalable macro solution it would be best to have the working ranges in the first columns, then the macro could iterate across all the ranges it finds in subsequent columns, stopping when it finds a blank cell. In my original answer I had For i = 1 To 3, which is always a bad sign in a macro, because it means the macro needs changing if more data is added.

In the attached workbook, I have created 2 new sheets. The first one 'Preferred' shows how I would do this using formulas, which is exactly what @ssaqibh suggested, so if you like this make sure he shares the points.

The second sheet ('Macro') uses the approach you asked for, but moves the working ranges to the start, so you can extend the data ranges as far as you like to the right in columns, and the macro will just pick up whatever is there (as long as the spacing is consistent). Copy-values-in-ranges-Rev.xlsm Copy-values-in-ranges-Rev.xlsm

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel sheet - Calculating 4 processors ! | 11 | 28 | |

Excel formula | 5 | 29 | |

VBA Help TT V-1.1 | 15 | 22 | |

Excel VBA, find a string in a column, update a cell | 7 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!