IsError showing Run-time error 91

dude32008

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
  2. MacOS
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
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
If you use the Match function, you can wrap it in WorksheetFunction.IsNA() to trap the error.

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

dude32008

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
  2. MacOS
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.
VBA Code:
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

New Member
Joined
Nov 4, 2020
Messages
3
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
  2. MacOS
If you use the Match function, you can wrap it in WorksheetFunction.IsNA() to trap the error.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Maybe
VBA Code:
    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

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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.
VBA Code:
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,236
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top