Why does this workbook crash every time i run the code

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
424
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I change a value in column J or 10 and the code starts to run then it crashes the workbook!
Any idea why?

VBA Code:
Sub BOReason()

    Dim Ws           As Worksheet
    Dim LRow         As Long
    Dim x            As Variant, y As Variant, i As Variant, MatchData As Variant
    Dim rng          As Range, Sourcerng     As Range, Comparerng As Range, Fill As Range

    
    On Error Resume Next
    Set Ws = ActiveSheet
    LRow = Ws.Range("A1").End(xlDown).Row
    Set rng = Ws.Range("A1:A" & LRow)
    
    With rng
     .AutoFilter 1, Format(Date, "dd/mm/yyyy"), 2, Format(Date - 1, "dd/mm/yyyy")
    End With

        
            Set Comparerng = Ws.Range("E2:E" & LRow)
            Set Sourcerng = Ws.Range("J2:J" & LRow)
            Set Fill = Ws.Range("J2:J" & LRow + 1)
            
            For Each x In Comparerng.SpecialCells(xlCellTypeVisible)
            For Each y In Comparerng.SpecialCells(xlCellTypeVisible)
            For Each i In Sourcerng.SpecialCells(xlCellTypeVisible)
            
            On Error Resume Next
            If x = y Then
            Fill = i
            End If
             Next i
              Next y
               Next x
                       
            Ws.AutoFilter.ShowAllData

        
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

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
Back
Top