How to populate Range variable from a Sub/Function call?
- by Ken Ingram
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
