Solved

# Populating an Array

Posted on 2005-04-07
152 Views
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
Question by:IndyBean

LVL 50

Accepted Solution

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 50

Expert Comment

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

### Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …