Run-time error '1004': Method 'Range' of object ' Worksheet' failed in VBA

Question

Sub Mark_Attendance()

Dim sh As Worksheet
Dim dsh As Worksheet

Set sh = ThisWorkbook.Sheets("Mark Attendance")
Set dsh = ThisWorkbook.Sheets("Database")

Dim r As Integer
Dim C As Integer

Dim lr As Long

For C = 13 To 19
For r = 4 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
If sh.Cells(r, C).Value <> "" Then

  If Application.WorksheetFunction.CountIf(dsh.Range("A:A"), sh.Range("A" & r).Value & "_" & Format(sh.Cells(3, C).Value, 0)) > 0 Then  
  If sh.Range("F1").Value = True Then  
    lr = Application.WorksheetFunction.Match(sh.Range("A" & r).Value & "_" & Format(sh.Cells(3, C).Value, 0), dsh.Range("A:A"), 0)  

  dsh.Range("A" & lr).Value = sh.Range("A" & r).Value & "_" & Format(sh.Cells(3, C).Value, 0)  
  dsh.Range("B" & lr).Value = sh.Range("A" & r).Value  
  dsh.Range("C" & lr).Value = sh.Range("B" & r).Value  
  dsh.Range("D" & lr).Value = sh.Range("C" & r).Value  
  dsh.Range("E" & lr).Value = sh.Range("D" & r).Value  
  dsh.Range("F" & lr).Value = sh.Range("E" & r).Value  
  dsh.Range("G" & lr).Value = sh.Range("F" & r).Value  
  dsh.Range("H" & lr).Value = sh.Range("G" & r).Value  
  dsh.Range("I" & lr).Value = sh.Range("H" & r).Value  
  dsh.Range("J" & lr).Value = sh.Range("I" & r).Value  
  dsh.Range("K" & lr).Value = sh.Range("J" & r).Value  
  dsh.Range("L" & lr).Value = sh.Range("K" & r).Value  
  dsh.Range("M" & lr).Value = sh.Range("L" & r).Value  
  dsh.Range("N" & lr).Value = sh.Cells(3, C).Value  
  dsh.Range("O" & lr).Value = sh.Cells(r, C).Value  

  End If  
Else  
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A")) + 1  

  dsh.Range("A" & lr).Value = sh.Range("A" & r).Value & "_" & Format(sh.Cells(3, C).Value, 0)  
  dsh.Range("B" & lr).Value = sh.Range("A" & r).Value  
  dsh.Range("C" & lr).Value = sh.Range("B" & r).Value  
  dsh.Range("D" & lr).Value = sh.Range("C" & r).Value  
  dsh.Range("E" & lr).Value = sh.Range("D" & r).Value  
  dsh.Range("F" & lr).Value = sh.Range("E" & r).Value  
  dsh.Range("G" & lr).Value = sh.Range("F" & r).Value  
  dsh.Range("H" & lr).Value = sh.Range("G" & r).Value  
  dsh.Range("I" & lr).Value = sh.Range("H" & r).Value  
  dsh.Range("J" & lr).Value = sh.Range("I" & r).Value  
  dsh.Range("K" & lr).Value = sh.Range("J" & r).Value  
  dsh.Range("L" & lr).Value = sh.Range("K" & r).Value  
  dsh.Range("M" & lr).Value = sh.Range("L" & r).Value  
  dsh.Range("N" & lr).Value = sh.Cells(3, C).Value  
  dsh.Range("O" & lr).Value = sh.Cells(r, C).Value  

  End If  
End If  

Next r
Next C

MsgBox "Attendance has been marked!!!", vbInformation

End Sub

]1

Answer 1

Start Microsoft Excel
On the Menu Bar select "File" > "[Excel] Options" > "Trust Center" > "Trust Center Settings..." > "Macro Settings"
In the top section, select the radio button for "Disable all macros with notification"
In the bottom section, check the box for "Trust access to VBA project object model"
Click "OK" and "OK" to save the settings
Close Excel

Hope You Find This Useful,
Peter

Answer 2

If you use variables for cells - always use long
So try to fix r and C Integer for long

Regards.

Answer 3

I have one user getting this error message where others that are using the same excel template file are not.