Solved

Virtually same code-but second version does not work

Posted on 2011-02-11
17
332 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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

828 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