Solved

For range, cells selected are far to the RIGHT of what is expected to be selected

Posted on 2011-03-10
4
216 Views
Last Modified: 2012-05-11
I keep having this same problem over and over and every time I think I have it solved, the darn thing crops up again.  It has soemthing to do with teh way Excel handles ranges.  Attached is code that I want to look in column H:H for a value, then when it finds it, simply check each field below it until it hits a blank field and this is the count for the rows  in the range.  However, rather than counting FOUR rows which is in the actual ranges, it counts the rows far to the RIGHT.  I just cannot get this to count the fields in the correct column.
Public Sub DynamicRangeDatasourceIndexOnly()
'It re-sets the entire range the datasources columns
'that are pulled from the Green Package and used in client reports.  That is, this worksheets holds
'the value that is on the report, the lookup values that are actually used and which columns the
'data is pulled from.  This worksheet is vital in being correct as it is used in data validation!
On Error GoTo ErrorHandler
'If user adds a new category, 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
Dim rngVarName          As Range

'First, find where the data source area starts
ThisWorkbook.Worksheets("Datasource").Activate
With ThisWorkbook.Worksheets("DataSource").Range("H:H")
    For Each varName In Array("ss_IDXCurrency", "ss_IDXCountry", "ss_IDXSector", _
                              "ss_IDXSectorbreakdown", "ss_IDXQuality", "ss_IDXQualitybreakdown")
    Set strRange = .Find(varName, LookIn:=xlValues)
    strAddress = strRange.Address
    Debug.Print strAddress
'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 + 2 'Start range below headers for group
            intEnd = (intRowNum + i) - 1  'Last row for end of range
            .Range("A" & intAddressNum & ":E" & intEnd).Name = varName 'Range name in workbook
    
'Sort data
        With ThisWorkbook.Worksheets("DataSource").Range(varName)
               .Select
               .Sort Key1:=Range(varName).Cells(1, 5), Order1:=xlAscending, Header:=xlGuess, _
               OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
               DataOption1:=xlSortNormal
            End With
    Next
End With

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " Description: " & Err.Description
    Resume Exit_ErrorHandler

End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Can you post a workbook?
0
 

Author Comment

by:ssmith94015
Comment Utility
Problem this is confidential data and the IT monitors web traffic.  I what is going on, why it is happening, I just can't figure out how to fix it!
0
 

Author Comment

by:ssmith94015
Comment Utility
StephenJR, finally figured out what the issues.  It was so sublte.  I have posted a version ofthe final code that works.  I went back to another post on a similar question and realzied I needed to change
ThisWorkbook.Worksheets("Datasource").Activate
With ThisWorkbook.Worksheets("DataSource").Range("N:N")
    For Each varName In Array("ss_WAL", "ss_EDB", "ss_CB")
    Set strRange = Find(varName, LookIn:=xlValues)

to
ThisWorkbook.Worksheets("Datasource").Activate
With ThisWorkbook.Worksheets("DataSource")
    For Each varName In Array("ss_WAL", "ss_EDB", "ss_CB")
    Set strRange = .Range("N:N").Find(varName, LookIn:=xlValues)

That is, the placment of the Range "N:N" was throwing off the range that was being looked at.  Sid, in anotehr question, along with Kevin, answered taht.  I simply did not make the connection that that was also the answer to this same question.  Thank you for your attention.
Public Sub DynamicRangeDatasourceYears()
'THIS IS FOR THE Datasourceworksheet only, first five columns, M:Q!
'It re-sets the entire range the datasources columns
'that are pulled from the Green Package and used in client reports.  That is, this worksheets holds
'the value that is on the report, the lookup values that are actually used and which columns the
'data is pulled from.  This worksheet is vital in being correct as it is used in data validation!
On Error GoTo ErrorHandler
'If user adds a new category, 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
Dim rngVarName          As Range
Dim intRowCounter       As Integer

'First, find where the data source area starts
ThisWorkbook.Worksheets("Datasource").Activate
With ThisWorkbook.Worksheets("DataSource")
    For Each varName In Array("ss_WAL", "ss_EDB", "ss_CB")
    Set strRange = .Range("N:N").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
        'Reset worksheet to original settings
        Do Until Worksheets("Datasource").Range(strAddress).Offset(i, 0) = ""
            .Range(strAddress).Offset(i, 0).Activate
            i = i + 1
        Loop
            intRowNum = .Range(strAddress).Row 'Row for varName address
            intAddressNum = intRowNum + 2 'Start range below headers for group
            intEnd = (intRowNum + i) - 1  'Last row for end of range
'A to E are used because these are the columns in the RANGE as defined
'by Excel's use of using A to designate the first column in any range internally
'This can be a GOTCHA if not understood what Excel is actually doing with the range
            .Range("N" & intAddressNum & ":S" & intEnd).Name = varName 'Range name in workbook
'Sort data
        With ThisWorkbook.Worksheets("DataSource").Range(varName)
               .Select
               .Sort Key1:=Range(varName).Cells(1, 6), Order1:=xlAscending, Header:=xlGuess, _
               OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
               DataOption1:=xlSortNormal
            End With
    
    Next
End With

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " Description: " & Err.Description
    Resume Exit_ErrorHandler

End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
Comment Utility
All sorted then? Good work.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

772 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

11 Experts available now in Live!

Get 1:1 Help Now