VBA copy cells paste in column +1

[Grubsing]

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

 

[JackDanIce]

Welcome to the board.

Try:

Sub DEA_v1()

Dim x As Long
Dim DMUNo As Range: Set DMUNo = Range("H28")

Application.ScreenUpdating = False

For x = 1 To 22
    DMUNo.Value = x
    SolverSolve UserFinish:=True
    Range("N" & Rows.Count).End(xlUp).Offset(1).Value = DMUNo.Offset(, 2).Value
    Cells(4, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(22).Value = Range("K4").Resize(22).Value
Next x

Set DMUNo = Nothing

End Sub

 

[JackDanIce]

Forgot to include the line:

Application.ScreenUpdating = True

before End Sub

 

[Grubsing]

Thanks for your reply. Somehow the code does not work though. Excel is not doing any operation when running the code..

 

[JackDanIce]

Is the sheet you need the macro to run on, the activesheet when you run the macro? I've added some comments too:

Sub DEA_v1()

Dim x As Long                       'Integers are automatically converted into Long types
Dim DMUNo As Range
Set DMUNo = Range("H28")            'Set variable DMUNo to be Range("H28")

Application.ScreenUpdating = False  'Disable screenupdating (macro runs faster)

For x = 1 To 22                     'Loop through values 1-22 sequentially
    DMUNo.Value = x                 'Set Range("H28") = value of x
    SolverSolve UserFinish:=True    'Run the solver model
    
    Range("N" & Rows.Count).End(xlUp).Offset(1).Value = DMUNo.Offset(, 2).Value
                                    ' set value of last used cell in column N to that of H28/DMUNo
    Cells(4, .Columns.Count).End(xlToLeft).Offset(, 1).Resize(22).Value = Range("K4").Resize(22).Value
                                    ' Paste values of K4:K25 to the next available column in row 4
Next x

Application.ScreenUpdating = False  'Reset screen updating

Set DMUNo = Nothing                 'Clear the range object before ending the macro

End Sub

 

[Grubsing]

If I understand it correctly, there's a mistake in the following line copied below. Here is what I would like my macro to do.

- In cell H28 a value for x is constantly chosen (x indeed varies from 1 until 22).
- For each value in H28, the solver model is run. The results will be shown in cells K4 until K25 but also in cell J28. Since the results of performing the solver in column K and cell J28 vary for each x we want these values copied and pasted.
--> The value of cell J28 should be copied and pasted in the column N (N4 is the first blank cell), so in column N there will be values after performing each solver operation for each x ranging from N4 until N25.
--> The other outcomes (K4 until K25) of running the solver should also be copied but these must be pasted in the columns after column N; from P onwards because column O is already in use.
- Then the macro should continue this operation for each x.
- All these operations are performed in the same worksheet.

I hope you can help me out! Thanks a lot in advance

Range("N" & Rows.Count).End(xlUp).Offset(1).Value = DMUNffset(, 2).Value
' set value of last used cell in column N to that of H28/DMUNo