Solved

Virtually same code-but second version does not work

Posted on 2011-02-11
17
323 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
  • 9
  • 8
17 Comments
 

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
ssmith94015: Can you upload your workbook?

Sid
0
 

Author Comment

by:ssmith94015
Comment Utility
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
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
I will wait for the dummy :)

Sid
0
 

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
ssmith94015: I checked your code.

It is assigning correct range to "ss_" & varName

What exctly is the problem?

Sid
0
 

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
DynamicRangeDataSource() works

Let me check DynamicRangeSettings()

Sid
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
Is that the reason why the code is failing?

Sid
0
 

Author Comment

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

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, got it.  Thank you, I would never have figured that one out!
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Do remember to change the same in DynamicRangeDataSource() as well :)

Sid
0
 

Author Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now