Run-Time Error 91

[mandukes]

I have this following piece of code extracted from a complex code.. Also, I'm have attached the excel file to save the time..

The problem is I'm getting the RunTimeError 91 Object Variable or With Block Variable not set..

When FindV is not found in the Range "B3" then it should skip and jump to next I.
The first time it searches when FindV =4 couldn't find it in the Range "B3" It jumped to Next I.
After that When FindV = 5
It's not ignoring and jumping to Next I, Instead it's throwing this "RunTimeError Object Variable or With Block Variable not set." Any help would be appreciated..



[TABLE="class: grid, width: 208, align: left"]
<tbody>[TR]
[TD="width: 104"][/TD]
[TD="width: 104"]A[/TD]
[TD="width: 104"]B[/TD]
[/TR]
[TR]
[TD="width: 104"]1[/TD]
[TD="width: 104"]Node[/TD]
[TD="width: 104"]Pipe[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Sub test()
For I = 1 To 10


Worksheets("NHSPC").Activate
Range("B3").Select
    FindV = I
     On Error GoTo L305
    Range(Selection, Selection.End(xlDown)).Select
      Selection.Find(What:=FindV, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
NCTPipe = ActiveCell.Offset(0, -1).Value


L305: Next I
End Sub

DropBox - Sample.xlsx

 

[shg]

Once you have an error, the state of execution is error handling, and that state is exited only with a Resume statement. Until that happens, you can't handle another error. There are a lot of ways to fix this; here's one.

Option Explicit

Sub test()
  Dim i             As Long
  Dim rFind         As Range
  Dim NCTPipe       As Variant

  Worksheets("NHSPC").Select

  With Range("B3", Cells(Rows.Count, "B"))
    For i = 1 To 10
      Set rFind = .Cells.Find(What:=i, _
                              LookIn:=xlValues, _
                              LookAt:=xlWhole)
      If Not rFind Is Nothing Then NCTPipe = rFind.Offset(, -1).Value
    Next i
  End With
End Sub

 

[mandukes]

Shg, Thank you so much You save the day by replying with this magical piece of code.. AWESOME!!!

 

[shg]

You're welcome.