Solved

Virtually same code-but second version does not work

Posted on 2011-02-11
17
336 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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