PDA

View Full Version : Sleeper: VBA code needed for creating references in Excel



PT2010
04-12-2010, 04:58 PM
I am working on setting up a model for linear optimization where cell references have to be created dynamically. I have been so far successful in creating the reference statically as shown bellow:



Sub CreateCNSTDIA()
Dim D As Range
Dim E As Range
Dim CC As Range
Dim i, j, k, l As Integer
k = 1
l = 1
ActiveWorkbook.Names.Add Name:="DecVar", RefersTo:="=42ORGWW!$P$4:$R$6"
ActiveWorkbook.Names.Add Name:="Cnstdia", RefersTo:="=42ORGWW!$O$25:$O$27"
Set D = Range("DecVar")
Set E = Range("Cnstdia")
For i = 1 To D.Rows.Count
For j = 1 To D.Columns.Count
If (i = j) Then
E.Cells(l, k).Formula = "=A1"
l = l + 1
End If
Next j
Next i
End Sub

It would be helpful if I don't have to hardcode "=A1" instead be assign the reference using logic.

PT2010
04-12-2010, 05:35 PM
Came up with a slighly better solution:


Sub CCNSTDIA()
Dim D As Range
Dim E As Range
Dim i, j, k, l As Integer
k = 1
l = 1
ActiveWorkbook.Names.Add Name:="DecVar", RefersTo:="=42ORGWW!$T$4:$V$6"
ActiveWorkbook.Names.Add Name:="Cnstdia", RefersTo:="=42ORGWW!$O$25:$O$27"
Set D = Range("DecVar")
Set E = Range("Cnstdia")
For i = 1 To D.Rows.Count
For j = 0 To D.Columns.Count
If (i = j) Then
E.Cells(l, k).Formula = D.Cells(i, j).Formula
l = l + 1
End If
Next j
Next i
End Sub


However, I had to copy the range to other location with cell referencing to the original range. I would like to create references using the original range. Any solutions?

p45cal
04-13-2010, 12:19 AM
I haven't the foggiest what you're trying to do but a stab in the dark might be:
E.Cells(l, k).Formula = "=" & D.Cells(i, j).Address
Also, take a look at the following which I'm fairly sure is giong to do the same sort of thing as your code but with less looping:

Sub CreateCNSTDIA2()
Dim D As Range
Dim E As Range
Dim i As Long
ActiveWorkbook.Names.Add Name:="DecVar", RefersTo:="=42ORGWW!$P$4:$R$6"
ActiveWorkbook.Names.Add Name:="Cnstdia", RefersTo:="=42ORGWW!$O$25:$O$27"
Set D = Range("DecVar")
Set E = Range("Cnstdia")
For i = 1 To Application.Min(D.Rows.Count, D.Columns.Count)
E.Cells(i).Formula = "=" & D.Cells(i, i).Address
Next i
End Sub

PT2010
04-13-2010, 06:49 AM
Thank You, p45cal!

Bob Phillips
04-13-2010, 07:52 AM
IMO, the better way to create range names is



With ActiveWorkbook.Worksheets("42ORGWW")
.Range("P4:R6").Name = "WDecVar"
.Range("O25:O27").Name = "Cnstdia"
End With

mdmackillop
04-13-2010, 09:52 AM
Hi PT
Welcome to VBAX.
When you post code, please format it using the green VBA button.
Regards
MD