Macros 101,

H

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
 
B

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

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

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

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
 
Top