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

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.

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

"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.
>
>

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.
>
>