Selecting a cell without cell referance

L

LB79

Hello.

Im trying to find a way to select a cell without the cell ref. Fo
example i have created a macro that searches for a particular word
When it find that word i want it to move across and clear the content
of the 5 cells next to it. When i try to record this it find th
correct cell but then when i movre accross it inserts cell refs.

Thank you
 
B

Bernard Liengme

Use the Offset function. Example fro VBA help:
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

Best wishes
 
J

Jazzer

Hi,

This VBA code should do the trick. Just change the phrase you ar
searhing for:

Sub FindNClear()
Cells.Find(What:="find this").Activate
ActiveSheet.Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), _
Cells(ActiveCell.Row, ActiveCell.Column + 6)).ClearContents
End Sub


- Asse
 
D

Dave Peterson

I would be a little more careful--just in case what you're looking for isn't
found:

Option Explicit
Sub FindNClear2()
Dim FoundCell As Range
With ActiveSheet.Cells
Set FoundCell = .Cells.Find(What:="find this", _
after:=.Cells(.Cells.Count), lookat:=xlWhole, _
LookIn:=xlValues, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Offset(0, 5).ClearContents
End If
End With
End Sub

And Find is one of those strange beasts that remember the settings from the last
time it was used. So it's probably a good idea to set them the way you want.
 
J

Jazzer

Dave said:
*I would be a little more careful--just in case what you're lookin
for isn't found:*
Click to expand...

Jeah, you're right. It's always good to have error checking.
*And Find is one of those strange beasts that remember the setting
from the last time it was used. So it's probably a good idea to se
them the way you want.*
Click to expand...

and that's right also. I just took off all the "extra" arguments fro
the FIND function, because it seems that MS has a habit t
change/add/remove those arguments from one Excel version to an other
So just for compatibility.

- Asse
 
Top