[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Virtually same code-but second version does not work

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
ssmith94015
Asked:
ssmith94015
  • 9
  • 8
1 Solution
 
ssmith94015Author Commented:
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
 
SiddharthRoutCommented:
ssmith94015: Can you upload your workbook?

Sid
0
 
ssmith94015Author Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
SiddharthRoutCommented:
I will wait for the dummy :)

Sid
0
 
ssmith94015Author Commented:
Attached is the workbook with both the code that works and the code that does not
TEST-VBADEVELOPMENTVERSION.xls
0
 
SiddharthRoutCommented:
ssmith94015: I checked your code.

It is assigning correct range to "ss_" & varName

What exctly is the problem?

Sid
0
 
ssmith94015Author Commented:
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
 
SiddharthRoutCommented:
DynamicRangeDataSource() works

Let me check DynamicRangeSettings()

Sid
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
Is that the reason why the code is failing?

Sid
0
 
ssmith94015Author Commented:
The data should be in column H on the Settings worksheet.
0
 
ssmith94015Author Commented:
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
 
SiddharthRoutCommented:
Ah got the error. Let me clean the code...

Give me few moments.

Sid
0
 
SiddharthRoutCommented:
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
 
ssmith94015Author Commented:
Yes, got it.  Thank you, I would never have figured that one out!
0
 
SiddharthRoutCommented:
Do remember to change the same in DynamicRangeDataSource() as well :)

Sid
0
 
ssmith94015Author Commented:
Will do and now that you have pointed out the problem, it all makes perfect sense.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now