I am having an "Error 91" issue. My code includes "Error Handling", but for some reason, after the cell search has failed once and correctly executed my code, a subsequent search failure causes the macro to bomb with "Error 91". It's as though a line of reset code is needed after the 1st failed search. I,m hoping someone is able to figure out what I,m doing wrong.
Thanks.
Here is my complete code...
Sub Confirm_Quicken_Data_Links()
' This macro searches the "Expenditure" sheet for each Payee Link held in "Quicken Data". Search results are stored in the "LinkTest".
' A check is made for Payees having a "2-Line" Header text.
Dim N As Integer
Dim X As String
Dim Y As Integer
Dim Row As Integer
Dim Col As Integer
Dim Payee As String
Dim PayeeAddrs As String
Dim PayeeLinkAddrs As String
Dim Payees As Integer
Dim ExpenseCategory As String
Dim LinkRange As Range
Sheets("Quicken Data").Select
N = 0
Range("B1").Select
Selection.End(xlToRight).Select
Payees = ActiveCell.Column - 1
Set LinkRange = Sheets("Expenditures").Range("LinkRange")
Range("B1").Offset(0, N).Select
Do While N <= Payees
Err.Clear
Payee = ActiveCell
PayeeAddrs = ActiveCell.Address
PayeeLinkAddrs = ActiveCell.Offset(15, 0).AddressLocal
PayeeAddrs = "'Quicken Data'!" & PayeeAddrs
X = "IFERROR(SEARCH(CHAR(10)," & PayeeAddrs & "),0)"
Range("SearchCell_2").Select ' Cell location for "Formula" search
ActiveCell.Formula = "=" & X
If ActiveCell.Value = 0 Then ' 1-LINE Header Text(Payee Name)
Y = 0
Else
Y = 1 ' 2-LINE Header Text(Payee Name)
End If
Sheets("Expenditures").Select
Range("A1").Select
On Error Resume Next
Cells.Find(What:=PayeeLinkAddrs, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
On Error GoTo NOTFOUND
If Err.Number = 0 Then ' No Error - Search String Found
Expense = ActiveCell.Offset(0, -5).Value
Sheets("LinkTest").Select
Range("A2").Offset(N, 0).Select
If Y = 1 Then
X = "Replace(" & PayeeAddrs & ", Search(CHAR(10)," & PayeeAddrs & "), 1, CHAR(32))"
ActiveCell.Formula = "=" & X
Else
ActiveCell = Payee
End If
Range("A2").Offset(N, 1) = PayeeLinkAddrs
Range("A2").Offset(N, 2) = Expense
Else
NOTFOUND:
Sheets("LinkTest").Select
Range("A2").Offset(N, 0).Select
If Y = 0 Then
X = "Replace(" & PayeeAddrs & ", Search(CHAR(10)," & PayeeAddrs & "), 1, CHAR(32))"
ActiveCell.Formula = "=" & X
Else
ActiveCell = Payee
End If
Range("A2").Offset(N, 1) = PayeeLinkAddrs
Range("A2").Offset(N, 2) = "Not Found"
End If
N = N + 1
Expense = ""
Sheets("Quicken Data").Select
Range(PayeeLinkAddrs).Offset(-15, 1).Select ' Next Payee
Loop
End Sub
