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

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?
0
IndyBean
Asked:
IndyBean
  • 2
  • 2
1 Solution
 
Dave BrettCommented:
You can dump one range to another directly, ie

Sub Copy()
    Range(Cells(1, 13), Cells(911, 13)).Offset(0, -2).Formula = Range(Cells(1, 13), Cells(911, 13)).Value
    ' same as
    'Range("K1:K911").Formula = Range("M1:M911").Value
    Range(Cells(1, 16), Cells(911, 16)).Offset(0, -2).Formula = Range(Cells(1, 16), Cells(911, 16)).Value
    ' same as
    'Range("N1:N911").Formula = Range("P1:P911").Value
End Sub

Cheers

Dave
0
 
IndyBeanAuthor Commented:
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.
0
 
IndyBeanAuthor Commented:
It used to work when I offst by (0,3) and (0,-7)
0
 
Dave BrettCommented:
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

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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