Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
I use a cell.find command to find a row col with a certain item. Now
I want to alter that so that its one row below after the find. Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
I'd try:
Set SortRange = ActiveSheet.Range(FirstCell.offset(1,0), LastCell) RocketMan wrote: I use a cell.find command to find a row col with a certain item. Now I want to alter that so that its one row below after the find. Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
After the Find statement:
FirstCell = FirstCell.Offset(1, 0).Address Then to get your sort range: Set LastCell = ActiveSheet.Range("E65536").End(xlUp).Address SortRange = Range(Firstcell & ":" & LastCell) "RocketMan" wrote: I use a cell.find command to find a row col with a certain item. Now I want to alter that so that its one row below after the find. Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
That should be Set SortRange = Range(FirstCell & ":" & LastCell)
And it wouldn't hurt to insert the worksheet name in the Set statement. "RocketMan" wrote: I use a cell.find command to find a row col with a certain item. Now I want to alter that so that its one row below after the find. Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
Not quite.
FirstCell = FirstCell.Offset(1, 0).Address will set the value to the offset address. So "pills" becomes $A$21. :) On Jan 30, 8:48*am, JLGWhiz wrote: After the Find statement: FirstCell = FirstCell.Offset(1, 0).Address Then to get your sort range: Set LastCell = ActiveSheet.Range("E65536").End(xlUp).Address SortRange = Range(Firstcell & ":" & LastCell) "RocketMan" wrote: I use a cell.find command to find a row col with a certain item. *Now I want to alter that so that its one row below after the find. *Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ * * * * :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ * * * * False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
final code that works...just for reference:
Sub Alpha_Click() Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) Set LastCell = ActiveSheet.Range("A65536").End(xlUp) Set SortRange = Range(FirstCell.Offset(1, 0), LastCell.Offset(0, 5)) SortRange.Sort Key1:=FirstCell End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probably too easy...change range to be one row down.
Did you not say you wanted the sort range to start one row lower than the
find cell? If so, then Offset(1, 0) is one row lower. If you want "pills" included in the sort range, the just use the FirstRow.Address as the front end of the range. Or give a better explanation of what you mean by one row down. "RocketMan" wrote: Not quite. FirstCell = FirstCell.Offset(1, 0).Address will set the value to the offset address. So "pills" becomes $A$21. :) On Jan 30, 8:48 am, JLGWhiz wrote: After the Find statement: FirstCell = FirstCell.Offset(1, 0).Address Then to get your sort range: Set LastCell = ActiveSheet.Range("E65536").End(xlUp).Address SortRange = Range(Firstcell & ":" & LastCell) "RocketMan" wrote: I use a cell.find command to find a row col with a certain item. Now I want to alter that so that its one row below after the find. Here is what I have so far: Dim FirstCell As Range Dim LastCell As Range Dim SortRange As Range Set FirstCell = ActiveSheet.Cells.Find(What:="Pills", After:=[A4], LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False) ' need to set FirstCell to be one row lower to sort the data Set LastCell = ActiveSheet.Range("E65536").End(xlUp) ' end of data lines will be in E column Set SortRange = ActiveSheet.Range(FirstCell, LastCell) ' ActiveSheet.SortRange.Sort ' use A column as key- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about RANGE (easy one, or at least it should be) | Excel Programming | |||
Question about RANGE (easy one, or at least it should be) | Excel Programming | |||
change colorIndex, help me write an easy macro | Excel Programming | |||
easy way to change a formula | Excel Discussion (Misc queries) | |||
Easy question - If ...then Change cell value | Excel Programming |