Runtime 1004 unable to get find property of range class

[Eric]

2 macros that are identical & in same module. They just look for different text within cells. Find " Apt" works great.
Find " #" returns Runtime 1004, unable to get find property of range class. Does anyone know what is going on? It also returns the error if I change the value I'm searching for and chane c to rng or anything else.


Sub Remove_AptNum()
Dim firstaddress As Variant
With Worksheets(1).Range("D")
Set c = .Find(what:=" Apt", LookIn:=xlValues)

If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(0, 1).Select
Selection.Value = Right(c, Len(c) - Application.WorksheetFunction.Search(" Apt", (c)))
c.Value = Left(c, Application.WorksheetFunction.Search(" Apt", c) - 1)

Set c = .FindNext(c)

If c Is Nothing Then Exit Do
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

End With
Set c = Nothing
End Sub

Sub remove_Numbersign()
Dim firstaddress As Variant
With Worksheets(1).Range("D")
Set c = .Find(what:=" #", LookIn:=x1Values)

If Not rng Is Nothing Then
firstaddress = b.Address
Do
rng.Offset(0, 1).Select
Selection.Value = Right(c, Len(c) - Application.WorksheetFunction.Search(" #", (c)))
rng.Value = Left(c, Application.WorksheetFunction.Search(" #", c) - 1)

Set rng = .FindNext(c)

If c Is Nothing Then Exit Do
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Set rng = Nothing
End Sub

 

[Andy Pope]

Hi Eric,

There are not identical.

The constant used on the .Find method is different.

Set c = .Find(what:=" Apt", LookIn:=xlValues)
Set c = .Find(what:=" #", LookIn:=x1Values)

Spot the subtle use of 1 (number one) in place of l (letter L).

Try using, Option Explicit, at the top of a code module, which will
catch typo's like this.

2 macros that are identical & in same module. They just look for different text within cells. Find " Apt" works great.
Find " #" returns Runtime 1004, unable to get find property of range class. Does anyone know what is going on? It also returns the error if I change the value I'm searching for and chane c to rng or anything else.


Sub Remove_AptNum()
Dim firstaddress As Variant
With Worksheets(1).Range("D")
Set c = .Find(what:=" Apt", LookIn:=xlValues)

If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Offset(0, 1).Select
Selection.Value = Right(c, Len(c) - Application.WorksheetFunction.Search(" Apt", (c)))
c.Value = Left(c, Application.WorksheetFunction.Search(" Apt", c) - 1)

Set c = .FindNext(c)

If c Is Nothing Then Exit Do
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

End With
Set c = Nothing
End Sub

Sub remove_Numbersign()
Dim firstaddress As Variant
With Worksheets(1).Range("D")
Set c = .Find(what:=" #", LookIn:=x1Values)

If Not rng Is Nothing Then
firstaddress = b.Address
Do
rng.Offset(0, 1).Select
Selection.Value = Right(c, Len(c) - Application.WorksheetFunction.Search(" #", (c)))
rng.Value = Left(c, Application.WorksheetFunction.Search(" #", c) - 1)

Set rng = .FindNext(c)

If c Is Nothing Then Exit Do
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
Set rng = Nothing
End Sub

--

Cheers
Andy

http://www.andypope.info

 

[Eric]

I drove myself crazy trying to figure out what I did wrong. It's embarrassing to have to post here to find a typo. Thank you for your help. Works fine now.