Use Find instead of a worksheet function
Sub Test()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rng = Sheet2.Range("C:C")
Set Value1 = Rng.Find(Object, lookat:=xlWhole)
If Value1 Is Nothing Then
Sheet1.Cells(i, "H") = "#N/A"
Else
Sheet1.Cells(i, "H") = Value1.Offset(, 6)
End If
Next
End Sub
You can't use RANGE as a variable name and the function does not return a value if nothing is found: the previous value is retained.
This should work, but I don't like it
Sub Test2()
On Error Resume Next
For i = 2 To 100
Object = Sheet1.Cells(i, "C").Value
Set Rnge = Sheet2.Range("C:I")
Value1 = "#N/A"
Value1 = Application.WorksheetFunction.VLookup(Object, Rnge, 7, False)
Sheet1.Cells(i, "H") = Value1
Next
End Sub