Use a named range in cell formula

[bayles]

Hi,

I want to insert a formula into columns AP:EE that is referencing a sheet named for example "Category Hiearchy (1)". The numerical value is dependent on the value in column AG.

I am almost there but cannot figure out how the insert <VALUE IN Range AG> into the defined name.

This is the code and I am hitting a snag with the last 2 lines. For arguments sake just have the value 5 in regrng.

Any help appreciated. Please let me know if it is unclear or needs sample data.

Thanks

---

Sub Test()


'INSERT FOMRULAS FOR VALIDATION


Set WS1 = Sheets("BvTrax")
Set rng = WS1.Cells.Find(What:="CleansedDescription", After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(0, 2)
startcol = rng.Column

' CREATING NAMES FOR EACH REGIONS CATEGORY HIERARCHY PAGES
ActiveWorkbook.Names.Add Name:="region1", RefersToR1C1:= _
"='CATEGORY HIERARCHY (1)'!R1:R1048576"
ActiveWorkbook.Names("region1").Comment = ""
ActiveWorkbook.Names.Add Name:="region2", RefersToR1C1:= _
"='CATEGORY HIERARCHY (2)'!R1:R1048576"
ActiveWorkbook.Names("region2").Comment = ""
ActiveWorkbook.Names.Add Name:="region3", RefersToR1C1:= _
"='CATEGORY HIERARCHY (3)'!R1:R1048576"
ActiveWorkbook.Names("region3").Comment = ""
ActiveWorkbook.Names.Add Name:="region4", RefersToR1C1:= _
"='CATEGORY HIERARCHY (4)'!R1:R1048576"
ActiveWorkbook.Names("region4").Comment = ""
ActiveWorkbook.Names.Add Name:="region5", RefersToR1C1:= _
"='CATEGORY HIERARCHY (5)'!R1:R1048576"
ActiveWorkbook.Names("region5").Comment = ""
Region = WS1.Cells.Find(What:="CleansedDescription", After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(0, -1).Column


For i = 1 To 5
DeptCol = WS1.Cells.Find(What:="Department" & i, After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Column
BranCol = WS1.Cells.Find(What:="Brand" & i, After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Column
rownum = WS1.Cells.Find(What:="Description", After:=Range("a1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).row

' INSERT FORMULA FOR TOP 5
Set rng = Range("A10").End(xlDown).Offset(1, startcol - 1 + ((i - 1) * (Max + 6)))
rng.FormulaR1C1 = "=IF(OR(ISERROR(MATCH(RC" & BranCol & "&RC" & DeptCol & "&R" & rownum & "C,BRANDS!C5,0)),RC" & DeptCol & "=""""),"""",HLOOKUP(""Category"",BRANDS!R1C1:R500000C4,MATCH(RC" & BranCol & "&RC" & DeptCol & "&R" & rownum & "C,BRANDS!C5,0),FALSE))"
Range(rng, Range("A100000").End(xlUp).Offset(0, startcol + 3 + ((i - 1) * (Max + 6)))).FormulaR1C1 = rng.FormulaR1C1



'INSERT FORMULA FOR ALL OTHER OPTIONS
Dim regrng As Range
Set regrng = form.Range(Cells(rownum + 1, Region), Cells(Rows.Count, Region).End(xlUp))
rng.Offset(0, 6).FormulaR1C1 = _
"=IF(RC" & DeptCol & "="""","""",HLOOKUP(RC" & DeptCol & ",regionID,R" & rownum & "C,FALSE),HLOOKUP(RC" & DeptCol & ",region2,R" & rownum & "C,FALSE))=0,"""",IF(RC" & Region & "=1,HLOOKUP(RC" & DeptCol & ",region1,R" & rownum & "C,FALSE),HLOOKUP(RC" & DeptCol & ",region2,R" & rownum & "C,FALSE))))"
Next i


End Sub