Can I replace blocks of data within an array rather than looping thru it.

[MrTeeny]

If I grab a block of data from excel into an array say A1:E5, is there anyway I can simply reference the data in the region B1:E5, I'd simply like to replace that section with the data from A1:D5.

If is was doing it on the sheet it'd simply be

Range("B1:E5").Value = Range("A1:D5").Value

but I'm stumped as how to reference it within the array.

 

[Joe4]

It looks to me like you are just moving the whole data set over one column.
Can you just insert a blank column in column A? Or is there data below it that should not move?
If that is the case, why not just use Cut/Paste instead?

 

[Joe4]

The Cut/Paste option would just look like:

    Range("A1:D5").Cut Range("B1")

 

[MrTeeny]

I'm basically doing an audit trail of the last few cells, I could do it by simply shifting the cells along one clipping off the last row and dumping new data into the first column but was hoping to do all calculations etc in an array and then just do one dump of data back to the sheet rather than lots of writing of cells to the sheet.

 

[Joe4]

I'm basically doing an audit trail of the last few cells, I could do it by simply shifting the cells along one clipping off the last row and dumping new data into the first column but was hoping to do all calculations etc in an array and then just do one dump of data back to the sheet rather than lots of writing of cells to the sheet.

In order to do that, you would need to do the something like the following in your VBA code:
1. Create an array
2. Iterate through the array and apply each calculation and store the result
3. Iterate through the array again, and populate each cell in your range

 

[MrTeeny]

Thanks, so it looks like you can't amend subsets of data within the array in one go, guess I'll have to loop thru and amend each item in the array separately if that's the case or maybe just shift the data A1:D5 to B1:E5 and do the new calcs on column A in an array so that can be dumped in one go to the sheet rather than each cell updated one at a time.

Thanks again

 

[Joe4]

Thanks, so it looks like you can't amend subsets of data within the array in one go, guess I'll have to loop thru and amend each item in the array separately if that's the case.

Arrays are not my forte (so I may be mistaken), but I believe that is the case. I believe each item of the array needs to be set explicitly.