Create named range for each column not working?

[Ed]

I'm attempting to loop through all the columns in the UsedRange and create a
named range for each one. It loops, and all strings are correct - I just
don't have any ranges when it's finished! What have I done wrong?

Ed

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
cntCol = ws.UsedRange.Columns.Count
cntRow = ws.UsedRange.Rows.Count

' Set name for each column range
cntRng = 1
For i = 1 To cntCol
strRng = i
strAddr = ws.Name & "!C" & strRng
strRng = "col" & strRng
wb.Names.Add _
Name:=strRng, _
RefersToR1C1:=strAddr
Next i

 

[Ed]

I checked under Insert>>Name>>Define, and all the names I just created are
there, and the addresses are correct - EXCEPT the show up as ="Sheet3!C1" ,
unlike other names that don't have the quotes. These names are not showing
up in the name box in the formula bar, although they are listed under
Define.

Does this help with a solution?

Ed

 

[Bob Phillips]

This works for me


Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
cntCol = ws.UsedRange.Columns.Count
cntRow = ws.UsedRange.Rows.Count

' Set name for each column range
cntRng = 1
For i = 1 To cntCol
strRng = i
strAddr = "=" & ws.Name & "!C" & strRng
strRng = "col" & strRng
wb.Names.Add _
Name:=strRng, _
RefersToR1C1:=strAddr
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

I forgot the "=" in the strAddr!! *sigh* Well, at least I know I had the
code right. Now if I could just *proof* it right!

Thanks, Bob.
Ed