运行时错误'1004'指定的值超出范围 [英] Run-Time error '1004' The specified value is out of range

问题描述

Sub FindInShapes1()   
Dim rStart As Range
Dim shp As Shape
Dim sFind As String
Dim sTemp As String
Dim Response

sFind = InputBox("Search for?")
If Trim(sFind) = "" Then
    MsgBox "Nothing entered"
    Exit Sub
End If
Set rStart = ActiveCell
For Each shp In ActiveSheet.Shapes
    sTemp = shp.TextFrame.Characters.Text
    If InStr(LCase(sTemp), LCase(sFind)) <> 0 Then
        shp.Select
        Response = MsgBox( _
          prompt:=shp.TopLeftCell & vbCrLf & _
          sTemp & vbCrLf & vbCrLf & _
          "Do you want to continue?", _
          Buttons:=vbYesNo, Title:="Continue?")
        If Response <> vbYes Then
            Set rStart = Nothing
            Exit Sub
        End If
    End If
Next
MsgBox "No more found"
rStart.Select
Set rStart = Nothing
End Sub

我编写了上面的Macro，以通过内部写的文字在带错的"工作表中查找excel形状.该宏可以在任何新书中使用，但不能在我需要的书中使用，因为它会继续显示以下消息:

I made the above Macro for finding excel shapes in a "crouded" worksheet, by the text written inside. The macro works in any new books but not in the one I need, were it keeps on showing the following message:

"Run-Time error '1004'
The specified value is out of range"

当我单击调试"时，它将突出显示以下行:

and as soon as i click on "Debug" it highlights the line:

sTemp = shp.TextFrame.Characters.Text

怎么了?

感谢您的帮助 基亚拉

推荐答案

我认为由于无法检查形状中是否存在 TextFrame ，因此您应该使用忽略该错误错误恢复下一个:

I think as there is no way to check for the existence of a TextFrame within a shape, you should ignore the error by using On Error Resume Next:

Sub FindInShapes1()
Dim rStart As Range
Dim shp As Shape
Dim sFind As String
Dim sTemp As String
Dim Response

On Error Resume Next

sFind = InputBox("Search for?")
If Trim(sFind) = "" Then
    MsgBox "Nothing entered"
    Exit Sub
    End If
    Set rStart = ActiveCell
    For Each shp In ActiveSheet.Shapes
        'If shp.TextFrame.Characters.Count > 0 Then
        If InStr(LCase(sTemp), LCase(sFind)) <> 0 Then
            shp.Select
            Response = MsgBox( _
                prompt:=shp.TopLeftCell & vbCrLf & _
                sTemp & vbCrLf & vbCrLf & _
                "Do you want to continue?", _
                Buttons:=vbYesNo, Title:="Continue?")
            If Response <> vbYes Then
                Set rStart = Nothing
                Exit Sub
            End If
        End If
        'End If
        sTemp = shp.TextFrame.Characters.Text

    Next
    MsgBox "No more found"
    rStart.Select
    Set rStart = Nothing
End Sub

`

这篇关于运行时错误'1004'指定的值超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！