How to populate Range variable from a Sub/Function call?

Posted by Ken Ingram on Stack Overflow See other posts from Stack Overflow or by Ken Ingram
Published on 2012-09-27T03:35:30Z Indexed on 2012/09/27 3:37 UTC
Read the original article Hit count: 254

Filed under:
|
|

I am trying to get this sub to work but the operationalRange variable is not being assigned. Despite the fact that the function selectBodyRow(bodyName) works fine.

Sub sortRows(bodyName As String, ByRef wksht As Worksheet)
  Dim operationalRange As Range

  Set operationalRange = selectBodyRow(bodyName)


  Debug.Print "Sorting Worksheet: " & wksht.Name

  If Not operationalRange Is Nothing Then
    operationalRange.Select
    Debug.Print "Sorting " & operationalRange.Count & "Rows."
    ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _
          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(wksht.Name).Sort
          .SetRange operationalRange
          .Header = xlGuess
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
    End With
  Else
   MsgBox "Body is not being Set"
  End If
End Sub

The Sub being called by the above Sub is:

Function selectBodyRow(bodyName As String) As Range
Dim rangeStart As String, rangeEnd As String
Dim selectionStart As Range, selectionEnd As Range
Dim result As Range, srchRng As Range, cngrs As Variant

If bodyName = "WEST" Then
    rangeStart = "<-WEST START->"
    rangeEnd = "<-WEST END->"
ElseIf bodyName = "EAST" Then
    rangeStart = "<-EAST START->"
    rangeEnd = "<-EAST END->"
End If

Set srchRng = Range("A:A")
srchRng.Select

Set selectionStart = srchRng.Find(What:=rangeStart, After:=ActiveCell, LookIn _
        :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
       xlNext, MatchCase:=False, SearchFormat:=False)

Set selectionEnd = srchRng.Find(What:=rangeEnd, After:=ActiveCell, LookIn _
        :=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
       xlNext, MatchCase:=False, SearchFormat:=False)

Set result = Range(selectionStart.Offset(1, 0), selectionEnd.Offset(-1, 0))
result.EntireRow.Select
End Function

© Stack Overflow or respective owner

Related posts about excel

Related posts about vba