Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
plz help: creating named range in VBA, loop goes haywire
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
plz help: creating named range in VBA, loop goes haywire
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range gives an error only when the filename is lo | Excel Discussion (Misc queries) | |||
Creating a named range? | Excel Discussion (Misc queries) | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
loop through all named ranges | Excel Programming | |||
Creating a Named Range using VB | Excel Programming |