The problem is in calling the function to convert a column number to a
column letter, as it changes the passed column number to 0 when the column
number is 25.

Simply change

Function UseCol(MyColNum)

to

Function UseCol(ByVal MyColNum)

--

HTH

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


"KR" wrote in message
...
I'm using the code below to try to add 111 named ranges (for the 111
columns
of data I have) so I can use those to populate my graphs, without having
to
edit/add each named range manually. This should be a one-time deal. (this
is
in addition to a handful of named ranges that already exist in the
workbook)

When I run the code below, I end up having my loop (namecol) not go
sequentially from 1 to 111; instead it just keeps repeating random sets of
numbers. Since the code is fairly short, I thought I'd be able to figure
out
what is going wrong, but I'm just plain stuck.I don't see anything that
would reset namecol to a lower value.

Can anyone take a quick look, and suggest what I might be doing wrong?

Thanks,
Keith


'---------------------------------------------------------------------------
-
Sub AddNamedRanges()

For NameCol = 1 To 111
Excel.Application.StatusBar = Str(NameCol)
NameColRef = UseCol(NameCol)
pop = ActiveWorkbook.Names.Add("AS1" & NameColRef, _
"=OFFSET(AllSummary!$A$4,AllSummary!$B$3-25," & Trim(Str(-1 +
NameCol))
& ",26,1)")
Next

End Sub

'---------------------------------------------------------------------------
-
Function UseCol(MyColNum)
ColMod = MyColNum Mod 26 'div column # by 26. Remainder is the
second letter
If ColMod = 0 Then 'if no remainder then fix value
ColMod = 26
MyColNum = MyColNum - 26
End If
intInt = MyColNum \ 26 'first letter
If intInt = 0 Then UseCol = Chr(ColMod + 64) Else _
UseCol = Chr(intInt + 64) & Chr(ColMod + 64)
End Function


'---------------------------------------------------------------------------
-

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.