PDA

View Full Version : Solved: Dynamical generate name ranges



Pete
06-08-2008, 03:29 AM
Hi

Is it possible to dynamically generate name ranges based on information in columns B and C......and that the name range always follow a set pattern....

=(Vol_Alternative_Suppliers_Other_Markets_EU * Price_Other_Markets_EU_Alternative_Suppliers) - (Vol_Alternative_Suppliers_Other_Markets_EU * (Price_Alternative_Suppliers_Other_Markets_EU + UFC_Alternative_Suppliers_Other_Markets_EU))

the only part of the formula tha changes is the supply customer name and the demand customer name........

in the shown example i.e.

Vol_Alternative_Suppliers_Other_Markets_EU

Alternative_Supplier = Supply customer and

Other_Markets_EU - Demand customer...

see attached workbook........

mdmackillop
06-08-2008, 03:51 AM
I may be completely misunderstanding this, and there would need to be code to set the RefersTo range.

Sub naming()
Set Rng = Range(Cells(6, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cel In Rng
If cel <> "" And cel.Offset(, 1) <> "" Then
ActiveWorkbook.Names.Add "Vol_" & cel.Text & "_" & cel.Offset(, 1).Text, _
RefersTo:="C:\AAA\[SIL-Model-7-c20c.xls]Modelling(Vol)!$F$6:$BA$6"
End If
Next
End Sub

Pete
06-08-2008, 05:14 AM
thanks for the feed back.......taking a different approach to the question...