Getting --unable to get the findnext property of the range class ERROR

[DeanJS]

Hey All,

I am new to the forum - dabble in VBA

I am receiving the following ERROR --unable to get the findnext property of the range class

My problem is this findnext has worked in this macro no problem till I get 2500 odd records into the file.

i have included my macro i Hope I have made the FINDNEXT bold lol

Sorry for how bad it is lol


Please if any 1 can see why please help me

thanks Dean

Sub Printer_code_Express_List()
'
Dim bk1 As Workbook, sh1 As Worksheet, cell1, cell11, ExpressPrinterId, OEMRRPPrice, OEMThumbImage, OEMDetailImage, OEMLargeImage As Range
Dim bk2 As Workbook, sh2 As Worksheet, cell2, PrinterId, CartridgeId, ExpressImage, Cartridgecode, DownIndicator, BudgetUpdatePriceChangeTarget As Range

Dim SupplierCodeColumn, BudgetLastCell As Range
Dim SizeOfExpressfile As Integer
Dim RowCounter As Integer
Dim OEMcodeColumn As Integer
Dim BudgetPriceChangeColumn As Integer
Dim BudgetPercentageColumn As Integer
Dim fName As Variant
Dim fNameAndPath As Variant
Dim Printeronlyfilename As String
Dim Printercartridgefilename As String
Dim SizeOfBudgetPriceList As Integer
Dim SearchValue As String
Dim BudgetLastRow As Long
Dim FirstFindAddress As String
Dim OEMCodeReference As String
Dim PrinterReference As String
Dim ActualSupplierCode As String
Dim SKUColumn As Integer
Dim ColumnCounter As Integer
Dim NewEntryLine As Integer
Dim PrinterColumn As Integer
Dim OEMRRPColumn As Integer
Dim OEMThumbImageColumn As Integer
Dim OEMDetailImageColumn As Integer
Dim OEMLargeImageColumn As Integer
Dim ExpressPrinterColumn As Integer
Dim ExpressRRPColumn As Integer
Dim ExpressImageColumn As Integer


SizeOfExpressfile = 11780
RowCounter = 2
OEMcodeColumn = 3
UpdateBudgetColumn = 34
SizeOfBudgetPriceList = 0
SKUColumn = 2
BudgetPriceChangeColumn = 36
BudgetPercentageColumn = 40
ActualSupplierCode = 0
OEMCodeReference = 0
PrinterReference = 0
FirstFindAddress = 0
BudgetLastRow = 0
SearchValue = 0
SizeOfBudgetPriceList = 0
Printercartridgefilename = 0
Printeronlyfilename = 0
ColumnCounter = 8
NewEntryLine = 2
PrinterColumn = 1
OEMRRPColumn = 14
OEMThumbImageColumn = 10
OEMDetailImageColumn = 11
OEMLargeImageColumn = 12
ExpressPrinterColumn = 35
ExpressRRPColumn = 3
ExpressImageColumn = 28

'Application.EnableEvents = False
'Application.DisplayAlerts = False
'Application.ScreenUpdating = False


MsgBox "Please Select Express Master Price Book file"
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select the Express Master Price Book File")
If fName = False Then Exit Sub

Set bk1 = Workbooks.Open(fName)
Set sh1 = bk1.Sheets("Sheet1")
Sheets("Sheet1").Unprotect "ABC123"
'Set OEMThumbImage = sh1.Cells(RowCounter, OEMcodeColumn)



MsgBox "Please Select PrinterCartridge_for_Express_Related_products File"
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select PrinterCartridge_for_Express_Related_products File")
If fNameAndPath = False Then Exit Sub

Set bk2 = Workbooks.Open(fNameAndPath)
Set sh2 = bk2.Sheets("Sheet1")
Sheets("Sheet1").Unprotect "ABC123"

Set OEMFindCode = sh2.Cells(RowCounter, SKUColumn)
SearchValue = OEMFindCode.Value
Set PrinterId = sh2.Cells(RowCounter, PrinterColumn)
PrinterReference = PrinterId.Value
Set CartridgeId = sh2.Cells(RowCounter, SKUColumn)
OEMCodeReference = CartridgeId.Value
Set Cartridgecode = sh2.Cells(RowCounter, OEMcodeColumn)

'Set ExpressImage = sh2.Cells(NewEntryLine, 1)
'Set CartridgeId = sh2.Cells(NewEntryLine, 2)



'Application.EnableEvents = False
'Application.DisplayAlerts = False
'Application.ScreenUpdating = False
'Application.Calculation = xlCalculationManual

bk1.Unprotect '  unprotects wookbook -- still causes issue- sheet had to be unprotected specifically
bk2.Unprotect '   unprotects wookbook -- still causes issue

bk1.Worksheets("Sheet1").Activate 'sets active sheet

Set sh1 = bk1.Sheets("Sheet1")
            BudgetLastRow = sh1.Range("T" & Rows.Count).End(xlUp).Row
            
        With sh2.Range("T1:T" & BudgetLastRow)
            
            Set BudgetLastCell = .Cells(.Cells.Count)
            
        End With

           
For RowCounter = 2 To SizeOfExpressfile

Set OEMFindCode = sh2.Cells(RowCounter, SKUColumn)
SearchValue = OEMFindCode.Value
Set PrinterId = sh2.Cells(RowCounter, PrinterColumn)
PrinterReference = PrinterId.Value
Set CartridgeId = sh2.Cells(RowCounter, SKUColumn)
OEMCodeReference = CartridgeId.Value
Set Cartridgecode = sh2.Cells(RowCounter, OEMcodeColumn)


'Set OEMFindCode = sh1.Cells(RowCounter, SKUColumn)'------ is set to wrong sheet !!!!!!!!!!!!!!
'SearchValue = OEMFindCode.Value


Set cell1 = sh1.Range("T1:T" & BudgetLastRow).Find(What:=SearchValue, _
          after:=BudgetLastCell, _
          LookIn:=xlValues, _
          LookAt:=xlWhole, _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, _
          MatchCase:=False)
           
            

            
If Not cell1 Is Nothing Then
        FirstFindAddress = cell1.Address
        TargetRow = cell1.Row '   REMEMBER SYNTAX TARGET - THEN SOURCE !!! LOL
        'MsgBox cell2.Value
        
        End If
        
        If cell1 Is Nothing Then
            
            Cartridgecode.Value = "Record Not Found in Express Price Book"
        
        'MsgBox cell2.Value
        
        End If
            
            
            
         
           
           
Do Until cell1 Is Nothing
           
If cell1 Is Nothing Then
           Exit Do
End If

If Not cell1 Is Nothing Then
    Set ExpressPrinterId = sh1.Cells(TargetRow, ExpressPrinterColumn)
    ExpressPrinterId.Value = PrinterReference
    
    
    Set cell1 = sh1.Range("T7:T" & BudgetLastRow).FindNext(after:=cell1)
    TargetRow = cell1.Row
        
End If
   
   
   
   
If cell1.Address = FirstFindAddress Then
    Exit Do
End If
   
Loop
   
   
Do
   
If CartridgeId.Value = OEMCodeReference Then
    RowCounter = RowCounter + 1
    Set CartridgeId = sh2.Cells(RowCounter, SKUColumn)
End If
   
If Not CartridgeId.Value = OEMCodeReference Then
   RowCounter = RowCounter - 1
   Exit Do
End If
   
   
Loop While CartridgeId.Value = OEMCodeReference
            

Next RowCounter