Solved

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

Posted on 2011-03-10
4
229 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
[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
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

636 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