VBA Run-time error '1004':

Question

Encountering Run-time error '1004': Method 'Range' of object '_Worksheet' failed

New to VBA Note: debug highligts the Locked = True lines of code. no merged cells in these ranges:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$6" Then

If Range("l6").Value = 0 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("K12:O31").ClearContents

Sheet4.Range("K12:031").Locked = True

Sheet4.Protect

End If

If Range("l6").Value = 1 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("L12:O31").ClearContents

Sheet4.Range("L12:031").Locked = True

Sheet4.Range("K12:K31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 2 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("M12:O31").ClearContents

Sheet4.Range("M12:031").Locked = True

Sheet4.Range("K12:L31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 3 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("N12:O31").ClearContents

Sheet4.Range("N12:031").Locked = True

Sheet4.Range("K12:M31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 4 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Range("O12:O31").ClearContents

Sheet4.Range("O12:031").Locked = True

Sheet4.Range("K12:N31").Locked = False

Sheet4.Protect

End If

If Range("l6").Value = 5 Then

Sheet4.Unprotect

Sheet4.Range("K12:O31").Locked = False

Sheet4.Protect

End If

End If

End Sub

Answer 1

You have mistyped the argument of the Range property. Using the last one as an example, you typed Sheet4.Range("O12:031").Locked = True (zero-three-one).

You meant to type Sheet4.Range("O12:O31").Locked = True (Oh-three-one).

This problem exists in cases 1 through 5. Consistency is often a virtue but not in this case.

Answer 2

Hi,

Here are some references that can help you-

https://community.spiceworks.com/how_to/195135-how-to-fix-run-time-error-1004-in-excel

https://excelchamps.com/vba/runtime-error-1004/

I hope this will help you.

Best Regards.