By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,247 Members | 871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,247 IT Pros & Developers. It's quick & easy.

Excel Error 1004 in vba vlookup function

P: 12
I hope I haven't posted this in the wrong section, but it is a VBA problem, so please forgive me if it should be elsewhere.
I have two tabs in a spreadsheet, both containing lists of parts codes. I want to programatically compare the first list against the second, and where the code isn't found in the second list I want to append it to the end of the list.
I have tried to trap the error 1004 that is raised when the part cannot be found in the list, and have created an on error goto jump to handle it, and this works on the first time it encounters the problem, but as soon as it hits it again, the error appears and the code stops.

Here is the section of code i have written so far.

Expand|Select|Wrap|Line Numbers
  1.     Sheets("Main").Select
  2.     Range("A1").Select
  3.     Range(Selection, Selection.End(xlDown)).Select
  4.     Selection.Name = "AllParts"
  5.     Sheets("ImportList").Select
  6.     Range("A1").Select
  7.     On Error GoTo AddPart
  8.     FRow = Cells(Application.Rows.Count, 1).End(xlUp).Row
  9. AddPart:
  10.     If Err.Number = 1004 Then
  11.     myPartCode = ActiveCell.Value
  12.     With Main
  13.     With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
  14.         .Value = myPartCode
  15.     End With
  16.     End With
  17.     End If
  18.  
  19.     ActiveCell.Offset(1, 0).Select
  20.     myPartCode = ""
  21.     On Error GoTo AddPart
  22.  
  23.     For i = ActiveCell.Row To FRow
  24.     myPartCode = Application.WorksheetFunction.VLookup(ActiveCell, Range("AllParts"), 1, False)
  25.  
  26.     Debug.Print myPartCode
  27.     ActiveCell.Offset(1, 0).Select
  28.     Next i
  29.  
The sheet named Main also has its codename set to Main.

I cannot understand why the on error captures it once, then doesn't capture it the second time.
This is completely baffling me, ane left me pulling out my hair.
Jan 10 '12 #1

✓ answered by NeoPa

Error code is supposed to reset the error. One of the Resume statements should be used. Using On Error to handle program flow is only an acceptable idea when you have a very good understanding of the Error handling facilities in VBA.

I would recommend Error Code that does what's expected and manage the flow of the logic entirely separately.

Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,474
Error code is supposed to reset the error. One of the Resume statements should be used. Using On Error to handle program flow is only an acceptable idea when you have a very good understanding of the Error handling facilities in VBA.

I would recommend Error Code that does what's expected and manage the flow of the logic entirely separately.
Jan 11 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.