Macros 101,

[Hirsch]

I am trying to locate a specific string of letters and go either up down
left or right of this string of letters and do some calculations.

for example.

find "Grand Total"
drop down one line and enter in this formula, then drop down one line and to
the right and enter in this formula.

(my problem is when the string of letters "Grand Total" floats from line 50
to line 60

[Bob Phillips]

If it is one column that you will look in, you could use

On Error Resume Next
iRow = Application.Match("Grand Total, Range("A:A"),0)
On Error Goto 0
If iRow > 0 Then 'found
Cells(iRow,"A").Offset(1,0).Formula = ??
Cells(iRow,"A").Offset(1,1).Formula = ??
...

If it is multi-column, take a look at Find in VBA Help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> I am trying to locate a specific string of letters and go either up down
> left or right of this string of letters and do some calculations.
>
> for example.
>
> find "Grand Total"
> drop down one line and enter in this formula, then drop down one line and
to
> the right and enter in this formula.
>
> (my problem is when the string of letters "Grand Total" floats from line
50
> to line 60

[Hirsch]

Bob, is there any formula similar to

Cells.offset(1,0).activate

I think this offset function could work, but I'm not familiar enough with it.




"Bob Phillips" wrote:

> If it is one column that you will look in, you could use
>
> On Error Resume Next
> iRow = Application.Match("Grand Total, Range("A:A"),0)
> On Error Goto 0
> If iRow > 0 Then 'found
> Cells(iRow,"A").Offset(1,0).Formula = ??
> Cells(iRow,"A").Offset(1,1).Formula = ??
> ...
>
> If it is multi-column, take a look at Find in VBA Help.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Hirsch" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to locate a specific string of letters and go either up down
> > left or right of this string of letters and do some calculations.
> >
> > for example.
> >
> > find "Grand Total"
> > drop down one line and enter in this formula, then drop down one line and
> to
> > the right and enter in this formula.
> >
> > (my problem is when the string of letters "Grand Total" floats from line
> 50
> > to line 60
>
>
>

[Bob Phillips]

You can't activate a cell from a formula, the nearest you get is that you
can point to another cell. Is that what you mean?

ActiveCell.Formula = "=" & ActiveCell.Offset(1, 0).Address(, , xlA1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> Bob, is there any formula similar to
>
> Cells.offset(1,0).activate
>
> I think this offset function could work, but I'm not familiar enough with
it.
>
>

[Hirsch]

Bob - Thank you

To be Crude you're a Freak'n Genius - If I could have you sitting over my
shoulder all of the time I would.

The final formula that I used was:

Cells.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(1, 0).Activate

ActiveSheet.Paste

Thank you - Thank you very much

Hirsch

"Bob Phillips" wrote:

> You can't activate a cell from a formula, the nearest you get is that you
> can point to another cell. Is that what you mean?
>
> ActiveCell.Formula = "=" & ActiveCell.Offset(1, 0).Address(, , xlA1)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Hirsch" <[email protected]> wrote in message
> news:[email protected]...
> > Bob, is there any formula similar to
> >
> > Cells.offset(1,0).activate
> >
> > I think this offset function could work, but I'm not familiar enough with
> it.
> >
> >
>
>
>