Assistance with VBA error handling - a cell not matching data set

[willow1985]

I was given the below VBA code and do not fully understand it. It runs when I enter a year in cell A1 on sheet "Score Card".

However if I enter in a year that has no data on sheet "Log" I get a Run-time error 1004: Application-defined or object-defined error.

I would like it if the year entered has no data on sheet "Log" that a Msg box comes up and states "No Data" and exits.

Any help would be appreciated! Thank you

Injury Data All Years.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	2023
2	Safety (Employees)
3	Key Performance Indicator	Operations		Area
4		Target	Actual	Cell	Parking Lot
5	Reportable	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	Non-Reportable	0	2	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7	Near Miss	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Score Card

Cell Formulas
Range	Formula
C5:C7	C5	=SUM(D5:X5)
D5:V5	D5	=COUNTIFS(Log[Area],'Score Card'!D4,Log[Year],'Score Card'!$A$1,Log[Reportable/ non-reportable],'Score Card'!$A$5)
D6:V6	D6	=COUNTIFS(Log[Area],'Score Card'!D4,Log[Year],'Score Card'!$A$1,Log[Reportable/ non-reportable],'Score Card'!$A$6)
D7:V7	D7	=COUNTIFS(Log[Area],'Score Card'!D4,Log[Year],'Score Card'!$A$1,Log[Reportable/ non-reportable],'Score Card'!$A$7)

Named Ranges
Name	Refers To	Cells
'Score Card'!Print_Area	='Score Card'!$A$1:$S$51	D5:V7

Injury Data All Years.xlsm
	A	B	C	D	E	F	G	H	I
1	First Name	Last Name	Year	Date	Building	Division	Reportable/ non-reportable	Area	Lost Time
2	M	D	2023	2023-01-06		Un	Non-Reportable	Cell	N
3	T	W	2023	2023-01-05		Un	Non-Reportable	Parking Lot	N
Log

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G3	Cell Value	="Near Miss"	text	NO
G2:G3	Cell Value	="Non-Reportable"	text	NO
G:G	Cell Value	="Near Miss"	text	NO
G2:G99860	Cell Value	="Non-Reportable"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:E1048576	List	='Graph Data'!$T$2:$T$7
F2:F1048576	List	='Graph Data'!$U$2:$U$13
G2:G1048576	List	='Graph Data'!$W$2:$W$4
H2:H3	List	='Graph Data'!$V$2:$V$40
I2:I1048576	List	='Graph Data'!$X$2:$X$3

Sub SC()
   Dim Cl As Range
   Dim Dic As Object
   Dim Yr As Long
 
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = 1
   Yr = Sheets("Score Card").Range("A1").Value
   With Sheets("Log")
      For Each Cl In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
         If Cl.Offset(, -5).Value = Yr Then Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("Score Card")
      .Range("D4:AA4").ClearContents
      .Range("D4").Resize(, Dic.Count).Value = Dic.Keys
   End With
   Columns("D:AA").EntireColumn.AutoFit
End Sub

 

[willow1985]

The problem is not in the macro.
The macro works fine. The problem is in the code that you have in the sheet that is activated when you modify cell A1
I also corrected that code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cl As Range
  Dim dic As Object
  Dim f As Range
 
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Address(0, 0) = "A1" Then
 
    Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = 1
    With Sheets("Log")
      Set f = .Range("C:C").Find(Target.Value, , xlValues, xlWhole)
      If f Is Nothing Then
        MsgBox "No Data"
        Target.Select
        Exit Sub
      End If
      For Each Cl In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
        If Cl.Offset(, -5).Value = Target.Value Then dic(Cl.Value) = Empty
      Next Cl
    End With
 
    Me.Range("D4:AA4").ClearContents
    Me.Range("D4").Resize(, dic.Count).Value = dic.Keys
    ActiveWorkbook.Worksheets("Score Card").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Score Card").Sort.SortFields.Add Key:=Range( _
      "D4:T4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
    With ActiveWorkbook.Worksheets("Score Card").Sort
      .SetRange Range("D4:Z4")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
    End With
    Columns("D:AA").EntireColumn.AutoFit
  End If
End Sub

I return the updated file.

Data All Years dam.xlsm

drive.google.com

-----

I apologize, I thought the issue was with the other. I did not write this one originally and still do not fully understand it.

Thank you so much for all of your help! Works perfect now