asked on

Populating an Array

Hi Experts,

I have an Inventory Simulation running where:

Inventory On Hand
- Sales
+ Replenishment

= New Inventory -----------Leads to----------> Next Weeks Replenishment

When the wek cycles I need to replace the Inventory on Hand column with the New Inventory Column and the Replenishment Column with the Next Weeks Replenishment column.

I am currently using an array for this.

For Materials = 0 to 911
Inventory(1, Materials) = MySheet.Cells(Materials, 13)
Order(1, Materials) = MySheet.Cells(Materials, 16)
Next Materials

For Materials = 0 to 911
MySheet.Cells(Materials, 11) = Inventory(1, Materials)
MySheet.Cells(Materials, 14) = Order(1, Materials)
Next Materials

Please tell me there is a more efficient way to move one column to another.

Can I stop the recalculation of the cells until I say refresh in code?
Can I copy Paste Values, then Copy paste bot in and then recalculate?

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IndyBean

ASKER

Sub CopyCells()

Range(Cells(6, 17), Cells(917, 17)).Offset(0, 37).Formula = Range(Cells(6, 17), Cells(917, 17)).Value
Range(Cells(6, 18), Cells(917, 18)).Offset(0, 37).Formula = Range(Cells(6, 18), Cells(917, 18)).Value
'Error 1004
Range(Cells(6, 20), Cells(917, 20)).Offset(0, -41).Formula = Range(Cells(6, 20), Cells(917, 20)).Value
Range(Cells(6, 21), Cells(917, 21)).Offset(0, -39).Formula = Range(Cells(6, 21), Cells(917, 21)).Value

End Sub

This worked untilI moved it further out.

IndyBean

ASKER

It used to work when I offst by (0,3) and (0,-7)

Dave

This code says

Range(Cells(6, 20), Cells(917, 20)).Offset(0, -41).Formula = Range(Cells(6, 20), Cells(917, 20)).Value

Take a range from T6:T917
and then go 37 columns to the left
and then paste this range

As you are in column 20 this can't be done

Cheers

Dave