PDA

View Full Version : VBA Copy cells paste +1 column



Decision
03-13-2015, 02:57 AM
Hi All,
I have a code in which solver is used to perform data envelopment analysis (some kind of operations method) for multiple decision making units (ranging from 1 to 22). However, in between solving for each dmu, I would like to copy the outcome generated by solver for each decision making unit and paste it into another column before the code performs the next solver operation for the other DMUs. Below you will find the code. In bold is what I tried to include already. For every DMU, I want to copy the values in cells K4:K25 and paste it for dmu1 in column P, for dmu2 in column Q, for dmu3 in column R in the same worksheet etc. until dmu 22. After copying the range of cells, the code should continue solving for the next dmu('s) - copy and paste values and continue solving until all operations are performed. Hope someone can help me out!


Sub DEA()
'Declare DMUNo as integer. This DMUNo represents the DMU under
'evaluation. In the example, DMUNo goes from 1 to 22
Dim DMUNo As Integer
For DMUNo = 1 To 22
'set the value of cell H28 equal to DMUNo (1,2, ..., 22)
Range("H28") = DMUNo
'Run the solver model.
SolverSolve UserFinish:=True
'Place the effieciency into column J
With Range("N3")
.Offset(DMUNo, 0) = Range("J28")
'Copy lambda's into column P ... AK
Range("K4:K25").Copy
Range("P4:P25").Select
ActiveSheet.Paste
End With
Next DMUNo
End Sub