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: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: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