Hello all,
am getting the above runtime error when the macro gets to this line:
Worksheets("sheet3").Range(Cells(7, a), Cells(b, a)).Select = WorksheetFunction.Transpose
any ideas, please help
Code:
Sub Optimal_Structure()
'This model finds the optimal structure for an agent structure.
Dim Agent_type As Variant
Dim Total_volume As Double
Dim levels As Double
Dim Agent_limit As Variant
Dim BOR_table As Variant
Dim v As Variant
Dim x As Variant
Dim i As Integer
Dim l As Integer
Dim level As Integer
Dim level_b As Integer
Dim level_e As Integer
Dim z As Variant
Dim level_table As Variant
Dim vol As Double
Dim volx As Double
Dim vol_remx As Double
Dim BOR As Double
Dim agent As Variant
Dim scen As Integer
Dim y As Variant
Dim a As Integer
Dim b As Integer
Dim c As Integer
'set agent type array
Total_volume = Sheets("constraints").Range("Total_volume").Value
Agent_type = Sheets("constraints").Range("agent_type").Value
Agent_limit = Range("Agent_limit").Value
BOR_table = Range("BOR_table").Value
levels = Range("levels").Value
l = 1
Do
'set table for each level
level = l
z = findrange(level) '<------------
level_b = z(0)
level_e = z(1)
'level_table = z.Value '<------------
'optimal structure at level l
i = 1
Do
If i = 1 Then
volx = Total_volume
Else
volx = vol_remx
End If
'Find max value in range and index.
v = ArrayMax(BOR_table, BOR_table, volx, level_b, level_e)
'MsgBox "Max value " & Format(v(0), "#.00") & " scenario number " & BOR_table(v(1), 1) & " scenario volume " & BOR_table(v(1), 2) & " agent " & BOR_table(1, v(2))
'calculate number of agents to take on this volume.
BOR = Format(v(0), "#.00")
vol = BOR_table(v(1), 2)
scen = BOR_table(v(1), 1)
agent = BOR_table(2, v(2))
x = agentlevels(volx, BOR, vol, scen, agent)
'MsgBox "level1 " & x(1) & " scenario number " & x(4) & " scenario volume " & x(3) & " agent " & x(1)
'define output array
y = Array(agent, vol, BOR, scen, x(1), x(2), x(3))
'output results into table
'Worksheets("sheet3").Range("c7:c" & 6 + UBound(y) + 1) = WorksheetFunction.Transpose(y)
a = 3 + i + ((l - 1) * 3)
b = 6 + UBound(y) + 1
Worksheets("sheet3").Range(Cells(7, a), Cells(b, a)) = WorksheetFunction.Transpose(y)
vol_remx = volx - x(2)
i = i + 1
Loop While vol_remx > 0
l = l + 1
Loop While l <= levels
' number_agents = Int(Total_volume / vol)
' volume_gone = vol * number_agents
' vol_remaining = Total_volume - volume_gone
End Sub
