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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>