select.find ERROR

cirugio

Board Regular
Joined
Mar 30, 2010
Messages
130
Hoping someone can assist. I currently have a module with a procedure which contains a Selection.Find command. I just use it to look up a value ("e-mail") and then replace contents of cells to the right of it using the offset command.
For some reason, I keep getting the following error "Object variable or With block variable not set" on the
Selection.Find... line. Anyone have thoughts on this? Thanks.


Sub ClearEmail()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Columns("A:A").Select<o:p></o:p>
Range("A1").Activate<o:p></o:p>
ActiveCell.Select<o:p></o:p>
<o:p></o:p>
Selection.Find(What:="e-mail", After:=ActiveCell, LookIn:=xlFormulas, LookAt _<o:p></o:p>
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _<o:p></o:p>
False).Activate<o:p></o:p>
<o:p></o:p>
ActiveCell.Offset(0, 7) = "------------"<o:p></o:p>
ActiveCell.Offset(0, 8) = "------------"<o:p></o:p>
ActiveCell.Offset(0, 9) = "------------"<o:p></o:p>
ActiveCell.Offset(0, 10) = "------------"<o:p></o:p>
ActiveCell.Offset(0, 11) = "------------"<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this

Code:
Sub ClearEmail()
Dim Found As Range
Set Found = Columns("A").Find(What:="e-mail", LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)
If Not Found Is Nothing Then Found.Offset(, 7).Resize(, 5).Value = "------------"
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Try this, which checks if what you are looking for is found before continuing.
Code:
Option Explicit
 
Sub ClearEmail()
Dim rngFnd As Range
Dim strLook As String
 
    strLook = "email"
 
    Set rngFnd = Range("A:A").Find(strLook)
 
    If rngFnd Is Nothing Then
        MsgBox "Not found"
    Else
        rngFnd.Offset(, 7).Resize(, 5) = "-------"
    End If
 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,145
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top