• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

EXCEL copy destination values

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
0
biker9
Asked:
biker9
  • 6
  • 4
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
The simplest way without code is to move the formulas down to row 24 and then copy it below each of the input set.

See attached.
Copy-of-Copy-values-in-ranges.xlsx
0
 
andrewssd3Commented:
@ssaqibh i right - I'm not sure why you don't just do this with formulas.  However if you do need the final values as plain numbers without formulas (why?), this code does it for your example.  Of course you say your final data will have many ranges, and this code is necessarily dependent on the format of the data you supply - there's not obvious way for the code to second-guess your layout.  So it will certainly need updating for the final data.  I've trie to make it obvious what you would change, but you might need to post the full spreadsheet at some point.
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
'

Open in new window

0
 
biker9Author Commented:
Thanks for the input, simply copying the formulas would work but I have a a large amount of them to copy, easier (i think) just to copy the variables, calc the result and paste results back where i need them.

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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
andrewssd3Commented:
Hi - I don't quite understand the new spreadsheet - the source ranges aren't the same shape as the destinations.  I really need to see exactly what your data is.  At the moment there is no easy way of guessing where it should be, as the source ranges have blank columns inside them.
0
 
Saqib Husain, SyedEngineerCommented:
You can still do it the way I suggested. The more data you add the more you copy the formulas.

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.
0
 
andrewssd3Commented:
Agreed - I think whichever method you use it would better to structure your data more carefully - having blocks spaced across the sheet can look more familiar, but it's much more efficient in Excel to organise it regularly by rows or columns.
0
 
biker9Author Commented:
Sorry but the copy formulas won't work for me, (thousands of formulas) but I did restructure the "entry" table a bit so no blank columns, the key is that the "source" entry table will always be the same size, and the  calculated results are expressed in tables that are always the same size, but they are different size then the "source" data entry table.


Copy-values-in-ranges-Rev-2.xlsx
0
 
andrewssd3Commented:
@biker9, sorry if I seem to be being dense here, but your last file posted still does not answer my question - the source ranges seem to bear no relation to to the result ranges.  Can you post some real data?  If not I don't see how any of the experts here can help you.
0
 
biker9Author Commented:
It is I who is  dense i am sure, I'm new to vba so I'm sure it was my lack of explanation that is confusing the issue.  I've managed to come up with a solution with a macro that works tho, not elegant, but it I think it will do the job.

Copy-values-in-ranges-Rev.xlsm
0
 
andrewssd3Commented:
What I am trying to understand is your comment in the question ' lots of ranges to calculate'. As the example stands at the moment there are just the three ranges, and the working ranges come after them on the sheet, so effectively blocking space for any more of you ranges.  

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
0
 
andrewssd3Commented:
I accidentally included two files there - they are both the same.
0
 
biker9Author Commented:
Works perfectly!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now