macro to select a range from a set cell to a specified cell

[Ez Duzit]

Hello all -

I have this macro that gets close to what I need, but falls short. I'm
trying to select a range from a found cell "A100" for example all the
way to a specified cell "A5", not just 20 rows up like my macro listed
below.

Thanks in advance.


Sub ActiveCellRange()

Cells.Find(What:= _
"Text to be found" _
, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:=True,
SearchFormat:=False). _
Activate
ActiveCell.Offset(-2, 0).Select
Dim MyRange As Range

Set MyRange = Range(ActiveCell, ActiveCell.Offset(-20, 0))

MyRange.Select

End Sub

 

[GS]

Try...

Sub SelectCellRange()
Dim sStartCell$, sFindText$, rng As Range

sStartCell = InputBox("Enter the address of the start cell")
sFindText = InputBox("Enter the text to find")
If sStratCell = "" Or sFindText = "" Then Beep: Exit Sub

Set rng = Cells.Find(What:=sFindText, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=True, SearchFormat:=False)
If Not rng Is Nothing Then _
Range(sStartCell, rng.Offset(-2).Address).Select
End Sub

...where sStartCell and sFindText must be strings representing the
address (A5) of the start cell for the selection *if* the search text
is found.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops! typo...

Sub SelectCellRange()
Dim sStartCell$, sFindText$, rng As Range

sStartCell = InputBox("Enter the address of the start cell")
sFindText = InputBox("Enter the text to find")

If sStartCell = "" Or sFindText = "" Then Beep: Exit Sub

Set rng = Cells.Find(What:=sFindText, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
If Not rng Is Nothing Then _
Range(sStartCell, rng.Offset(-2).Address).Select
End Sub

..where sStartCell and sFindText must be strings representing the
address (A5) of the start cell for the selection *if* the search text
is found.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ez Duzit]

Thanks Garry,

That's exactly what I need. Is there a way to make it automatic so
that I don't have to make any inputs? This will be a standard macro
that won't change. It's always going to find the same text and will
select range from there to "A5".

Ez

 

[GS]

Thanks Garry,

That's exactly what I need. Is there a way to make it automatic so
that I don't have to make any inputs? This will be a standard macro
that won't change. It's always going to find the same text and will
select range from there to "A5".

Ez

Try...

Sub SelectCellRange()
Dim rng As Range

Const sStartCell$ = "A5" '//edit to suit
Const sFindText$ = "some text" '//edit to suit

Set rng = Cells.Find(What:=sFindText, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=True, SearchFormat:=False)
If Not rng Is Nothing Then _
Range(sStartCell, rng.Offset(-2).Address).Select
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ez Duzit]

That got the job done!

I had to make a workaround for the Excel failing to wrap and size rows
for long sentences in merged cells and this was the last piece to the
puzzle. I really appreciate your help and support Garry.

Sincerely,

EZ

 

[GS]

That got the job done!

I had to make a workaround for the Excel failing to wrap and size
rows for long sentences in merged cells and this was the last piece
to the puzzle. I really appreciate your help and support Garry.

Sincerely,

EZ

You're welcome! Always glad to help when/where I can! I appreciate the
feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion