使用Find:Method'Range'of Object'_Worksheet'failed“ [英] Using Find: Method 'Range' Of Object '_Worksheet' failed"
问题描述
错误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屋!