0

I have an excel where I have to check if an account is present in which of two account listings. Else, I have to go to the next account. The code I am using is:

For x=1 to acc_num
Sheets("List1").Select
Range("A1:A100").Select
On Error GoTo CheckList2
Selection.Find(what:=x).Activate
GoTo Found

CheckList2:
Sheets("List2").Select
Range("A1:A100").Select
On Error GoTo NotFound
Selection.Find(what:=x).Activate

Found:
'More Code to execute if account is found

NotFound:
On Error GoTo 0
Next x

When an account is not found in List1 I still get the error pop-up: Run-time '91' : Object variable or With block not set.

I am unable to see why the error handling is not doing its job. Please help me out.

Community
  • 1
  • 1
user3794950
  • 23
  • 5
  • First thing I suggest you: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select). Second - use something like this: 1) `Set rng = Selection.Find(what:=x)` 2) `If Not rng Is Nothing Then ...` – Dmitry Pavliv Jul 06 '14 at 06:34
  • Thanks a lot :)...did the trick for me...will take care about the Select/Active the next time I start a VBA code (This will take a lot of time If I have change these things in this code and I'm too lazy :P) – user3794950 Jul 06 '14 at 07:31

1 Answers1

0

It looks like your error handlers are getting into each other's way. Before you initiate a new error handling procedure, clear any existing errors with 

On Error GoTo -1

MSDN entry for On Error GoTo is here: http://msdn.microsoft.com/en-us/library/5hsw66as.aspx 

Sub test()
Dim x, acc_num As Integer
x = 1
acc_num = 10
For x = 1 To acc_num
Sheets("Sheet1").Select
Range("A1:A100").Select
On Error GoTo CheckList2
Selection.Find(what:=x).Activate
GoTo Found

CheckList2:
Sheets("Sheet2").Select
Range("A1:A100").Select
On Error GoTo -1
On Error GoTo NotFound
Selection.Find(what:=x).Activate

Found:
'More Code to execute if account is found

NotFound:
On Error GoTo -1
Next x
End Sub
nwhaught
  • 1,562
  • 1
  • 15
  • 36