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
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