Go Premium for a chance to win a PS4. Enter to Win

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

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

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
ssmith94015
Asked:
ssmith94015
  • 2
  • 2
1 Solution
 
StephenJRCommented:
Can you post a workbook?
0
 
ssmith94015Author Commented:
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
 
ssmith94015Author Commented:
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
 
StephenJRCommented:
All sorted then? Good work.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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