find text in a cell and make cell 2 cells below that active

hello

my second post here, still learning VBA.
My question is pretty much included in the subject.
I need to find a cell in worksheet that contains text "descr1" - there is
only 1 cell like this in the worksheet - it is in a header, and make cell 2
cells below that active

any help is greatly appreciated

Hi,

Try this:

Cells.Find(What:="descr1", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Cells(ActiveCell.Row + 2, ActiveCell.Column).Select

Regards,
Stefi

„shark102” ezt �*rta:

> hello
>
> my second post here, still learning VBA.
> My question is pretty much included in the subject.
> I need to find a cell in worksheet that contains text "descr1" - there is
> only 1 cell like this in the worksheet - it is in a header, and make cell 2
> cells below that active
>
> any help is greatly appreciated
>

ok Stefis method works, now I need to name this workshhet after value in
selected cell

sorry not to have written it in the first post

"Stefi" wrote:

> Hi,
>
> Try this:
>
> Cells.Find(What:="descr1", After:=ActiveCell, LookIn:=xlValues, LookAt _
> :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> False).Activate
> Cells(ActiveCell.Row + 2, ActiveCell.Column).Select
>
> Regards,
> Stefi
>
> „shark102” ezt �*rta:
>
> > hello
> >
> > my second post here, still learning VBA.
> > My question is pretty much included in the subject.
> > I need to find a cell in worksheet that contains text "descr1" - there is
> > only 1 cell like this in the worksheet - it is in a header, and make cell 2
> > cells below that active
> >
> > any help is greatly appreciated
> >

I got it, no more help needed, thanks everybody for the effort.

"shark102" wrote:

> ok Stefis method works, now I need to name this workshhet after value in
> selected cell
>
> sorry not to have written it in the first post
>
> "Stefi" wrote:
>
> > Hi,
> >
> > Try this:
> >
> > Cells.Find(What:="descr1", After:=ActiveCell, LookIn:=xlValues, LookAt _
> > :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:= _
> > False).Activate
> > Cells(ActiveCell.Row + 2, ActiveCell.Column).Select
> >
> > Regards,
> > Stefi
> >
> > „shark102” ezt �*rta:
> >
> > > hello
> > >
> > > my second post here, still learning VBA.
> > > My question is pretty much included in the subject.
> > > I need to find a cell in worksheet that contains text "descr1" - there is
> > > only 1 cell like this in the worksheet - it is in a header, and make cell 2
> > > cells below that active
> > >
> > > any help is greatly appreciated
> > >

Hi

Dim Sht as worksheet
Dim FoundCell as Range

'replace with your workbook and worksheet if required
Set Sht = Activeworkbook.Activeworksheet

With Sht
Set FoundCell = .Find("descr1", .Cells(1, 1), , , xlByColumns)
end with
If not FoundCell is Nothing then
Sht.Activate 'if not active already
FoundCell.offset(2,0).Activate
end if


Highlight Find in the editor and hit F1 key to look at its parameters
regards
Paul

shark102 wrote:
> hello
>
> my second post here, still learning VBA.
> My question is pretty much included in the subject.
> I need to find a cell in worksheet that contains text "descr1" - there is
> only 1 cell like this in the worksheet - it is in a header, and make cell 2
> cells below that active
>
> any help is greatly appreciated