Rachel Chia
Rachel Chia

Reputation: 259

Range Object Error

I am currently trying to use Find Function to find 'One site:' From there, I will clear whatever is on the right. I am facing an application defined error on the line where it is suppose to clear. I think I might have done it wrongly. It would be good if someone told me where I have done it wrongly

Dim r As Range 
Set r = Sheet2.Range("E:M").Find(What:="One site:", _ 
After:=Sheet2.Range("E3"), _ 
LookIn:=xlValues, LookAt:=xlPart)
If Not r Is Nothing Then
r.End(xlRight).ClearContents 'Error on this line 
End If

Upvotes: 0

Views: 36

Answers (2)

PatricK
PatricK

Reputation: 6433

This should be what you are after, you need to change xlRight to xlToRight, and a little extra to clear those on the right.

Option Explicit

Sub SO45316709()
    Dim r As Range, r2 As Range
    On Error Resume Next
    Set r = Sheet2.Range("E:M").Find(What:="One site:", After:=Sheet2.Range("E3"), LookIn:=xlValues, LookAt:=xlPart)
    On Error GoTo 0
    If Not r Is Nothing Then
        Set r2 = r.End(xlToRight)
        Sheet2.Range(r.Offset(0, 1), r2).ClearContents
    End If
End Sub

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96791

You need a space before the underscores.

Upvotes: 1

Related Questions