Swissbones
New Member
- Joined
- Sep 30, 2011
- Messages
- 2
Hi all,
VBA noob here. I am trying to set up a LP problem in excel and I am having trouble setting up a constraint matrix. I need hlep inserting formulas into cells.
Bascially what I am after is:
In cell, Cells(20 + Count2, 4 + Count + p), I want the formula "=range.value*R[20+count2]C[3]"
any ideas?
Thanks...
VBA noob here. I am trying to set up a LP problem in excel and I am having trouble setting up a constraint matrix. I need hlep inserting formulas into cells.
Bascially what I am after is:
In cell, Cells(20 + Count2, 4 + Count + p), I want the formula "=range.value*R[20+count2]C[3]"
any ideas?
Thanks...
Code:
Sub InitializeLP()
Dim i As Double, j As Range, Proxies As Double, Row As Integer, RName As String, UColLim As Integer, Count As Integer, NumbSU As Integer, RangeNames() As String, k As Integer, p As Integer, RRange As Range
Dim Count2 As Integer, Count3 As Integer
Proxies = 5
UColLim = 34
NumbSU = 2030 - 2024 + 1
Count = 0
ReDim RangeNames(0 To Proxies - 1)
' Defines Production Name Ranges
For Each j In Range(Cells(3, "B"), Cells(3 + Proxies - 1, "B"))
Row = j.Row
RName = j.Value & "_Range"
ActiveWorkbook.Names.Add Name:=RName, RefersTo:=Range(Cells(3 + Count, 4), Cells(3 + Count, UColLim))
RangeNames(Count) = RName
Count = Count + 1
Next j
' Generates LP Matrix
For i = 0 To Proxies * NumbSU
Cells(20 + i, 3) = 1
Next i
Count2 = 0
For i = 0 To Proxies - 1
RName = RangeNames(i)
For p = 0 To NumbSU - 1
Count = 0
For Each RRange In Range(RName)
Cells(20 + Count2, 4 + Count + p) = RRange.Value * Cells(20 + Count2, 3)
Count = Count + 1
Next RRange
Count2 = Count2 + 1
Next p
Next i
End Sub