Type mismatch when trying to find a value in another sheet for update

[VBA_novice_Mic]

In short I have 2 files that are being compared. A CheckFile that is a check register and a VendorFile which is only 2 columns "Vendor_Name" and "Print on Check as".

The goal is to replace the Vendor_Name in the check file with the appropriate "Print on Check as" in order to upload to the bank for Positive pay name Matching.

Check file the name is in Column E. The Vendorfile Name is column A and "Print on Check as" in B.

I get the type mismatch on the set fValue line.

Sub VendorNameFix()
'
'Correct payee field values for Bee Accoounts Payable check files
'Runing this macro on any other file may cause posipay to fail matching names
'
Dim CheckFile As String
Dim VendorFile As String
Dim WS As Worksheet
Dim R, R1, RC As Long
Dim Vendor, Payee As String
Dim fValue As Variant
Dim Cell As Range
Dim Finder As Office.FileDialog
    
    VendorFile = "VENDOR_MATCH"
    CheckFile = ActiveWorkbook.Name
    ' Find rent payment and remove it from the posipay file
    On Error Resume Next
    Cells.Find(what:="CROSSROADS PLAZA", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).EntireRow.Delete
    On Error GoTo VendFind
OpenUp:
    Workbooks.Open "X:\BANK\Positie Pay\" & VendorFile & ".xlsx", , True
    GoTo MovingOn
VendFind:
   Set Finder = Application.FileDialog(msoFileDialogFilePicker)
 
    With Finder
    
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .Title = "Browse to select the Vendor Payeee Matchin file"
        .AllowMultiSelect = False
        
        If .Show = True Then
 
            VendorFile = .SelectedItems(1)
            VendorFile = Dir(VendorFile)
            VendorFile = Left(VendorFile, (InStr(VendorFile, ".") - 1))
 
        End If
 
End With
    MsgBox VendorFile
    Workbooks.Open "X:\BANK\Positie Pay\" & VendorFile & ".xlsx", , True
MovingOn:
    Workbooks(CheckFile).Activate
    Workbooks(VendorFile & ".xlsx").Activate
    For Each WS In Sheets
    Cells(2, 1).End(xlDown).Select
    RC = ActiveCell.Row
    For Each Cell In Workbooks(VendorFile & ".xlsx").Sheets("sheet1").Range("A2:A" & RC)
    
    Set fValue = Workbooks(CheckFile).Sheets("sheet1").Range("E:E").Find(Cell.Value, xlValues, xlWhole, xlByRows, False, False)
    If fValue Is Nothing Then GoTo NextCell
    If Cell.Value = fValue Then
        ActiveCell.Offset(0, 1).Copy
        Workbooks(CheckFile).Sheets("sheet1").PasteSpecial xlPasteValues
    End If
  
    
NextCell:
    Next Cell
    Next WS
Exit Sub


End Sub

Any help would be appreciated.

I just seem to be stumped at this point.

Thank you

 

[StephenCrump]

Welcome to the Forum!

It looks like your .Find arguments aren't lined up correctly:

.Find(Cell.Value, , xlValues, xlWhole, xlByRows, False, False)

 

[VBA_novice_Mic]

Thank you.

I looked that over several times and missed it every time.

That error is fixed.. Now I am getting application-defined or object-defined error on the paste. I have attempted it with both the "Paste:=" and without. Both generate the same error.

Could that be due to not directly defining the paste cell location?

 

[StephenCrump]

Could that be due to not directly defining the paste cell location?

Correct. You've specified the worksheet, but not the range within that worksheet.

 

[VBA_novice_Mic]

Thought so.. Working on that issue now.

Testing now I see I have a mismatch in the copy paste portion. Trying to get the correct paste address for the data copied is proving to be a bit stubborn, but I see the issue. So I think I can resolve the issue quickly.

I appreciate the help, I will report back once I have it resolved or hit a mental block again.

Thank you for your quick responses.

 

[VBA_novice_Mic]

@StephenCrump Thank you for your help. The code now runs as intended.

The only way I can cause a fault in the execution is by having more than one check to a single vendor. This is essentially by design as I am searching for each vendor name in the vendor match file for replacement only once. I doubt this will be an issue in practice.

If I find it to be occurring, it could be resolved buy putting the loop on the check file rather than the name file, or repeating the paste portion until it generates an error.

Thank you again!

 

[StephenCrump]

That's great, well done. Thanks for the update.