Creating Named Ranges via VBA Issue

[EDGE99]

I have the need to create several hundred named ranges and have created a VBA script that will loop through and create them. However it puts quotes on each end of the definition when creating it and then I have to go in and delete the beginning quote and ending quote for each one..

Do While ws.Cells(xRow, 1).Value <> ""
    Range1 = ws.Range("B" & xRow)
    RangeName = ws.Range("E" & xRow)
  
    ActiveWorkbook.Names.Add Name:=RangeName, RefersToR1C1:=Range1
    ActiveWorkbook.Names(RangeName).Comment = ""
    
    xRow = xRow + 1
Loop

Result: ="OFFSET('US Discounts Level 5'!$AW$3,0,0,COUNTA('US Discounts Level 5'!$AW:$AW),1)"

Need: =OFFSET('US Discounts Level 5'!$AW$3,0,0,COUNTA('US Discounts Level 5'!$AW:$AW),1)

Really hoping to find a way so I don't have to edit hundreds of named ranges..

 

[SkipVought]

hi,

Please post an example of the sheet that you are assigning Named Ranges to.

 

[zelgar]

Add .Formula to the end of the selected range

 Range1 = ws.Range("B" & xRow)[COLOR=#EF2929].Formula[/color]