英文:
Excel/vba: How to skip to next sheet if error?
问题
我正在尝试从每个工作表中查找“HFM”，如果存在，将一些名称粘贴在其后。所以问题是如何在工作表中没有“HFM”的情况下避免添加这些名称？
我首先在代码中没有使用On Error Resume Next，但那时会出现运行时错误。当添加它时，它会将名称复制到所有工作表中。如果从没有“HFM”的工作表开始，它也会返回运行时错误。那么我该如何解决这个问题？
Sub Ta()
Dim ws As Worksheet
For Each ws In Worksheets
Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
On Error Resume Next
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "name1"
' etc.
请注意，这是您提供的VBA代码的翻译部分。
英文:
I'm trying to Find "HFM" from every worksheet and if there is one paste some names after that. So the problem is how to avoid adding the names to the sheet when there is no "HFM" in the sheet?
I first had the code without On Error Resume Next but then there is runtime error. When adding that it copies the names to all sheets. Also it returns runtime error if started from sheet that doesn't have "HFM". So how do I fix this?
Dim ws As Worksheet
For Each ws In Worksheets
Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
On Error Resume Next
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "name1"
' etc.
答案1
得分: 0
代替 "On Error Resume Next"，您需要根据结果声明一个名为 "proceed" 的变量。以下是一个示例。
Dim ws As Worksheet
For Each ws In Worksheets
' 搜索文本
Set MyObjective = ws.Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not MyObjective Is Nothing Then
' 如果找到文本，则执行任务
MyObjective.Offset(0, 3).Value = "name1"
End If
Next ws
英文:
Instead of On Error Resume Next you will have to declare a variable proceed based on result. An Example below.
Dim ws As Worksheet
For Each ws In Worksheets
' Search for a text
Set MyObjectvie = ws.Cells.Find(What:="HFM", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not MyObjectvie Is Nothing Then
' if the text is found do the task
MyObjectvie.Offset(0, 3).Value = "name1"
End If
Next ws
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论