Using Find: Method 'Range' Of Object '

Question

I want to write a macro, that in all sheets locks certain cells -- from A12 to last row of R. Thing is, that I get

error 1004: "Method 'Range' Of Object '_Worksheet' failed"

in line

LastRow = wSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).

Could anyone help me out? Thanks!

Option Explicit

Sub ProtectAll()

Dim wSheet          As Worksheet
Dim Pwd             As String
Dim LastRow         As Integer

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
LastRow = wSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    wSheet.Range(Cells(12, 1), Cells(LastRow, 18)).Select
    wSheet.Protect Password:=Pwd, AllowFiltering:=True
Next wSheet

End Sub

Answer 1

Your code will fail if the sheet is blank as it currently assumes that it finds at least one non blank cell when it sets LastRow.

Try using a range object instead, test that it is Not Nothing before using LastRow.

Updated: for completeness added a check to see if sheets were already protected, and if so skips and otes these

Option Explicit

Sub ProtectAll()

Dim wSheet          As Worksheet
Dim Pwd             As String
Dim rng1 As Range
Dim strProt As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
Set rng1 = wSheet.Cells.Find(What:="*", After:=wSheet.[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng1 Is Nothing Then
With wSheet
If .ProtectContents Then
strProt = strProt & .Name & vbNewLine
Else
    .Range(.Cells(12, 1), .Cells(rng1.Row, 18)).Locked = True
    .Protect Password:=Pwd, AllowFiltering:=True
End If
End With
End If
Next wSheet

If Len(strProt) > 0 Then MsgBox strProt, , "These sheet were already protected so were skipped"

End Sub