Solved

Virtually same code-but second version does not work

Posted on 2011-02-11
17
335 Views
Last Modified: 2012-05-11
I created a dynamic range procedure that works perfectly.  However, when I copied the code to use on another worksheet, it does not seem to recognize the counter i.  I changed the following:  A:A to H:H, the varName strings to find and E to M as the range end.  But it simply does not do the loop.  It does find the address for the first string in the array, but then jumps from the DO UNTIL line to the intRowNum = .Range(StrAddress).Row line.  There are about 23 arows after the where the string is first found, so I do not know why it simply refuses to work.  I have attached both versions, the DynamicRangeDataSource (which does work) and the Dynamic RangeSettings (which does not work) procedures.
Public Sub DynamicRangeDataSource()
'If user adds or deletes a category in the ranges, this dynamically determines what the
'new ranges will be.  Need to lock the column headers just to be sure.
Dim strRange            As Range
Dim strAddress          As String
Dim varName             As Variant
Dim i                   As Integer
Dim intEnd              As Integer
Dim intRowNum           As Integer
Dim intAddressNum       As Integer
'First, find where the data source area starts
With Worksheets("DataSource").Range("A:A")
    For Each varName In Array("MarketData", "Characteristics", "Sectorbreakdown", "Qualitybreakdown")
    Set strRange = .Find(varName, LookIn:=xlValues)
    strAddress = strRange.Address
'Initialize loop counter to seed its value
    i = 1
        'Loop through the fields until a blank row is hit, this is the end of the range
        Do Until .Range(strAddress).Offset(i, 0) = ""
            i = i + 1
        Loop
            intRowNum = .Range(strAddress).Row 'Row for varName address
            intAddressNum = intRowNum + 1 'Start range below headers for group
            intEnd = (intRowNum + i) - 1  'Last row for end of range
            .Range("A" & intAddressNum & ":E" & intEnd).Name = "ss_" & varName 'Range name in workbook
    Next
End With
End Sub
Public Sub DynamicRangeSettings()
'If user adds a new Bond profile, this dynamically determines what the
'new ranges will be.  Need to lock the column headers just to be sure.
Dim strRange            As Range
Dim strAddress          As String
Dim varName             As Variant
Dim i                   As Integer
Dim intEnd              As Integer
Dim intRowNum           As Integer
Dim intAddressNum       As Integer
'First, find where the data source area starts
With Worksheets("Settings").Range("H:H")
    For Each varName In Array("ACTIVE PORTFOLIOS", "INDEX STANDARD PORTFOLIOS", "INDEX SUPERFUND PORTFOLIOS", _
                                "ACTIVE PORTFOLIO MASTER STATS", "ACTIVE PORTFOLIO MASTER STATS", " INDEX PORTFOLIO MASTER STATS")
    Set strRange = .Find(varName, LookIn:=xlValues)
    strAddress = strRange.Address
'Initialize loop counter to seed its value
    i = 1
        'Loop through the fields until a blank row is hit, this is the end of the range
        Do Until .Range(strAddress).Offset(i, 0) = ""
            i = i + 1
        Loop
            intRowNum = .Range(strAddress).Row 'Row for varName address
            intAddressNum = intRowNum + 1 'Start range below headers for group
            intEnd = (intRowNum + i) - 1  'Last row for end of range
            .Range("A" & intAddressNum & ":M" & intEnd).Name = "ss_" & varName 'Range name in workbook
    Next
End With
End Sub

Open in new window

0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 

Author Comment

by:ssmith94015
ID: 34874913
Ok, for some reason when the variable i  = 1 is used in the offiset, it returns cell O9!  What would caues it to jump to this column?  I have removed all the merged areas that I can find.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34874983
ssmith94015: Can you upload your workbook?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34875014
Unfortunately this is confidential data so cannot do that.  I did a .Range(strAddress).Select and while the strAddress is H8, it selects O8.  There are no range names that even have this column in them and I removed all merged fields.  This is the strangest thing.    Actually, let me dummy up some fake data and then I will post if I can clean it out enough.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875030
I will wait for the dummy :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 34875088
Attached is the workbook with both the code that works and the code that does not
TEST-VBADEVELOPMENTVERSION.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875133
ssmith94015: I checked your code.

It is assigning correct range to "ss_" & varName

What exctly is the problem?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34875134
In the file there is a typo for the Range(A:AH) I think.  Even when this is correct back to Range(H:H) it still goes to cell O8.  What is strange is if I change this to A:A and look for a testing value, the code works.  Could it be due to the fact I am using a column that is not A?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875142
DynamicRangeDataSource() works

Let me check DynamicRangeSettings()

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875154
I couldn't find the values

ACTIVE PORTFOLIOS", "INDEX STANDARD PORTFOLIOS", "INDEX SUPERFUND PORTFOLIOS" etc in the Sheet. Which column is it in?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875163
Is that the reason why the code is failing?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34875183
The data should be in column H on the Settings worksheet.
0
 

Author Comment

by:ssmith94015
ID: 34875198
Maybe it is my system, but it always jumps to field O8 rather than going to the row below H8.  Actually, I am getting too frustrated with this and simply changed the column to look in colunn A:A and added strings that the code does seem to find.  
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875222
Ah got the error. Let me clean the code...

Give me few moments.

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34875298
While I am cleaning the code let me tell you what the problem is....

You code starts with

With Worksheets("Settings").Range("H:H")

and then all the subsequet codes that start with a "." are creating the problem for example

.Range(strAddress).Select is considered as

With Worksheets("Settings").Range("H:H").Range(strAddress).Select and not

With Worksheets("Settings").Range(strAddress).Select

Hope you got the problem...

Sid
0
 

Author Closing Comment

by:ssmith94015
ID: 34875339
Yes, got it.  Thank you, I would never have figured that one out!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875356
Do remember to change the same in DynamicRangeDataSource() as well :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 34875409
Will do and now that you have pointed out the problem, it all makes perfect sense.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
A WS within a WS 11 39
create different version if file exists 1 44
Excel - Active X Checkboxes Groups 45 33
Zip Codes Excel Spreadsheet 4 17
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question