plz help: creating named range in VBA, loop goes haywire

[KR]

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

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

 

[Rob Bovey]

Hi Keith,

It looks to me like the problem is that you're modifying the loop
counter from the AddNamedRanges subroutine inside the UseCol function.
Change the declaration of the UseCol argument like so and the calling
subroutine will no longer "see" any modifications made to this argument:

Function UseCol(ByVal MyColNum)
'''
End Function

Note the "ByVal" in front of the MyColNum argument. This tells VBA not
to let the calling procedure see any changes made to that argument within
the UseCol function.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Bob Phillips]

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)