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

VBA_novice_Mic

New Member
Joined
Mar 3, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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.

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,731
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

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

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

VBA_novice_Mic

New Member
Joined
Mar 3, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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?
 

VBA_novice_Mic

New Member
Joined
Mar 3, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 3, 2022
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
@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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,731
Office Version
  1. 365
Platform
  1. Windows
That's great, well done. Thanks for the update.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,298
Messages
5,853,136
Members
431,549
Latest member
NnAa

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