Solved

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

Posted on 2011-03-10
4
222 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
ID: 35098814
Can you post a workbook?
0
 

Author Comment

by:ssmith94015
ID: 35101202
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
ID: 35102104
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
ID: 35107712
All sorted then? Good work.
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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 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