Solved

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

Posted on 2011-03-10
4
221 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

777 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