IsError showing Run-time error 91

[dude32008]

I am trying to match accounts from one sheet with a master list in another sheet. I am then trying to past info from the corresponding account to three columns to the right in my master sheet but when I a have an account that does not exist in the master I get a run time error.

Here is the code that I have. Any help is appreciated

If IsError(Cells.Find(What:=FindCell).Offset(0, 6)) = True Then
        FindCell = 0
       
    Else
   
            Cells.Find(What:=FindCell).Offset(0, 6).Select
            If Columny > 0 Then
                ActiveCell.value = Columnx & "/" & Columny
   
            Else
                ActiveCell.value = Columnx
       
            End If
            Range("B2").Select
    End If

I have tried to use the application.match function in vba but that has cause many errors. Thanks in advance

 

[JB2020]

If you use the Match function, you can wrap it in WorksheetFunction.IsNA() to trap the error.

If WorksheetFunction.IsNA(WorksheetFunction.Match(value, range, 0)) Then
    'No match found
Else
    'Match found
End If

 

[dude32008]

I have tried the worksheet function match and I come across a different runtime error, 1004 'unable to get the match property of the worksheet function class'.

Here is all the code I have written for the part of the macro that isn't working.

Dim Columnx As Long
    Dim Columny As Long
    Dim Count As Integer
    Dim NumRows As Integer
    Dim FindCell As Long
    Dim Loc As Integer
   
    Count = 0
   
    'count the number of cells with Account Numbers
    Sheets("PasteAccountsAndIPlan").Select
    NumRows = Range("B17", Range("B17").End(xlDown)).Rows.Count
    'Loop until all accounts have been verified
    Do
       
        FindCell = Cells(17 + Count, 2)
        Columnx = Cells(17 + Count, 5)
        Columny = Cells(17 + Count, 6)
       
        'Match Accounts in PasteAccountsAndIPlan with Stop Light Report
        Sheets("Stop Light Report").Select
        If WorksheetFunction.IsNA(WorksheetFunction.Match(FindCell, Range("B:B"), 0)) = True Then
            FindCell = 0
        Else
            Loc = WorksheetFunction.Match(FindCell, "B:B", 0)
            Range("L1").Select
            ActiveCell.Offset(Loc, 0).Select
            If Columny > 0 Then
                ActiveCell.value = Columnx & "/" & Columny
            Else
                ActiveCell.value = Columnx
            End If
           
        Range("B2").Select
        End If
       
           
        Count = Count + 1
        Sheets("PasteAccountsAndIPlan").Select
        Columny = 0
        Columnx = 0
    Loop Until Count = NumRows

I cant stop feeling like I am missing something obvious.

 

[dude32008]

If you use the Match function, you can wrap it in WorksheetFunction.IsNA() to trap the error.

If WorksheetFunction.IsNA(WorksheetFunction.Match(value, range, 0)) Then
    'No match found
Else
    'Match found
End If

I tried that but it still is kicking out an error with the match function, 1004 unable to get the Match property of the worksheetfunction class

 

[Fluff]

Maybe

    Dim Columnx As Long
    Dim Columny As Long
    Dim Count As Integer
    Dim NumRows As Integer
    Dim FindCell As Long
    Dim Loc As Integer
    Dim Fnd As Range
    
    Count = 0
   
    'count the number of cells with Account Numbers
    Sheets("PasteAccountsAndIPlan").Select
    NumRows = Range("B17", Range("B17").End(xlDown)).Rows.Count
    'Loop until all accounts have been verified
    Do
       
        FindCell = Cells(17 + Count, 2)
        Columnx = Cells(17 + Count, 5)
        Columny = Cells(17 + Count, 6)
       
        'Match Accounts in PasteAccountsAndIPlan with Stop Light Report
        Sheets("Stop Light Report").Select
        Set Fnd = Range("B:B").Find(FindCell, , , xlWhole, , , False, , False)
        If Not Fnd Is Nothing Then
            If Columny > 0 Then
                Fnd.Offset(, 10).Value = Columnx & "/" & Columny
            Else
                Fnd.Offset(, 10).Value = Columnx
            End If
           
            Range("B2").Select
        End If
       
           
        Count = Count + 1
        Sheets("PasteAccountsAndIPlan").Select
        Columny = 0
        Columnx = 0
    Loop Until Count = NumRows

 

[JB2020]

I have tried the worksheet function match and I come across a different runtime error, 1004 'unable to get the match property of the worksheet function class'.

Here is all the code I have written for the part of the macro that isn't working.

Dim Columnx As Long
    Dim Columny As Long
    Dim Count As Integer
    Dim NumRows As Integer
    Dim FindCell As Long
    Dim Loc As Integer
  
    Count = 0
  
    'count the number of cells with Account Numbers
    Sheets("PasteAccountsAndIPlan").Select
    NumRows = Range("B17", Range("B17").End(xlDown)).Rows.Count
    'Loop until all accounts have been verified
    Do
      
        FindCell = Cells(17 + Count, 2)
        Columnx = Cells(17 + Count, 5)
        Columny = Cells(17 + Count, 6)
      
        'Match Accounts in PasteAccountsAndIPlan with Stop Light Report
        Sheets("Stop Light Report").Select
        If WorksheetFunction.IsNA(WorksheetFunction.Match(FindCell, Range("B:B"), 0)) = True Then
            FindCell = 0
        Else
            Loc = WorksheetFunction.Match(FindCell, "B:B", 0)
            Range("L1").Select
            ActiveCell.Offset(Loc, 0).Select
            If Columny > 0 Then
                ActiveCell.value = Columnx & "/" & Columny
            Else
                ActiveCell.value = Columnx
            End If
          
        Range("B2").Select
        End If
      
          
        Count = Count + 1
        Sheets("PasteAccountsAndIPlan").Select
        Columny = 0
        Columnx = 0
    Loop Until Count = NumRows

I cant stop feeling like I am missing something obvious.

How about using countif first to check, before using match? Hopefully that will do the trick.

If WorksheetFunction.Countif(Range("B:B"), FindCell) > 0 Then
    'Match found
Else
    'No match found
End If