Create named range for each column not working?
 

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

range for each column - more info
 

I checked under InsertNameDefine, 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

"Ed" wrote in message
...
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

Create named range for each column not working?
 

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" wrote in message
...
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

Create named range for each column not working?
 

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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