how to handle repetitive errors in excel vba?

[Mahesh C A]

Dear All,

I written small VB code to check codes are existing in other sheet or not.

If code is not exiting in other sheet it should populate "not eligible" instead of "eligible"

I have 3 sheets in workbook (sheet1: 5000 ID's along with other columns as a PIM and column A as a PIM1 / sheet3: 100 ID's to check these ID's existing in sheet1 or not while using below mentioned VBA code.

In sheet3 some ID's are not existing in sheet1, for those ID's I need to update as a " not eligible" in sheet3. whenever n2 is "#N/A" it should populate "not eligible" in sheet3

Do while Cells(i+1, 1).Value <> ""
n1 = Cells(i+1, 1).Value
n2 = Application.Index(PIM, Application.Match(Cells(i+1, 1), PIM1, 0), 1)
If n1 = n2 Then
Cells(i+1, 7).Value = "Eligible"
Else
Cells(i+1, 7).Value = " Not eligible"
End If
i=i + 1
Loop

if n2 is an error I am getting Run time error '13' Type mismatch, not able to continue for further.

Thank you in advance,

Best Regards,
Mahesh

 

[Rick Rothstein]

I believe this macro will do what you want...

[table="width: 500"]
[tr]
	[td]Sub CheckIDs()
  Dim Addr As String
  Addr = "A1:A" & Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
  Sheets("Sheet3").Range(Addr).Offset(, 1) = Evaluate("IF(ISNUMBER(MATCH(Sheet3!" & Addr & ",Sheet1!A:A,0)),""eligible"",""not eligible"")")
End Sub[/td]
[/tr]
[/table]