使用Find：Method'Range'of Object'_Worksheet'failed“ [英] Using Find: Method 'Range' Of Object '_Worksheet' failed"

问题描述

我想写一个宏，在所有表中锁定某些单元格 - 从A12到最后一行R. Thing是，我得到

错误1004：方法'对象的范围'_Worksheet'失败

在行

LastRow = wSheet.Cells.Find（what：=*，After：= [A1]，SearchOrder = = xlByRows，SearchDirection = = xlPrevious） ）。

有人可以帮我吗？谢谢！

  Option Explicit 
 
 Sub ProtectAll（）
 
 Dim wSheet As Worksheet 
 Dim Pwd As String 
 Dim LastRow As Integer 
 
 Pwd = InputBox（输入您的密码以保护所有工作表，密码输入）
对于每个wSheet在工作表
 LastRow = wSheet.Cells.Find（什么：=*，之后：= [A1]，SearchOrder：= xlByRows，SearchDirection：= xlPrevious）.Row 
 wSheet.Range（Cells 12，1），Cells（LastRow，18））。选择
 wSheet.Protect密码：= Pwd，AllowFiltering：= True 
下一个wSheet 
 
 End Sub 
  

解决方案

如果表格为空，则代码将失败，因为它目前假定找到至少一个非空白单元格，当它设置 LastRow 。

尝试使用范围对象，测试它使用 LastRow 之前是不需要。

更新：为了完整，添加了一个检查，看看是否sh eets已经受到保护，如果是这样，请跳过这些

  Option Explicit 
 
 Sub ProtectAll（）
 
 Dim wSheet As Worksheet 
 Dim Pwd As String 
 Dim rng1 As Range 
 Dim strProt As String 
 
 Pwd = InputBox（输入您的密码以保护所有工作表，密码输入）
工作表中的每个wSheet 
设置rng1 = wSheet.Cells.Find（什么：=*，之后：= wSheet 。[A1]，SearchOrder：= xlByRows，SearchDirection：= xlPrevious）
如果不是rng1是没有，然后
用wSheet 
如果.ProtectContents然后
 strProt = strProt&名称与vbNewLine 
 Else 
 .Range（.Cells（12，1），.Cells（rng1.Row，18））。Locked = True 
 .Protect Password：= Pwd，AllowFiltering：= True 
如果
结束
结束如果
下一个wSheet 
 
如果Len（strProt）> 0 Then MsgBox strProt，这些工作表已被保护，因此被跳过
 
 End Sub 
  

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

解决方案

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

这篇关于使用Find：Method'Range'of Object'_Worksheet'failed“的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！