excel - 如何解决 VBA Excel 代码运行时错误 91

Question

我是宏的初学者。当我尝试执行代码时，我收到一条运行时错误 91 消息

Sub Worksheet_Functions()

    Dim HighestScore As Integer
    Dim LowestScore As Integer

    Dim TopScore As Range
    Dim BottomScore As Range

    LowestScore = WorksheetFunction.Min(Range("B1", "B5"))
    HighestScore = WorksheetFunction.Max(Range("B1", "B5"))

    Set TopScore = Range("B1,B5").Find(What:=HighestScore)
    Set BottomScore = Range("B1,B5").Find(What:=Lowesscore)

    Range("A7").Value = "lowest score is "
    Range("A8").Value = "Highest score is"

    Range("B7").Value = BottomScore.Offset(-1, 0).Value
    Range("B8").Value = TopScore.Offset(-1, 0).Value        

End Sub

标签： excelvba

Answer 1

我修复了你的代码。请看评论

Option Explicit

Sub Worksheet_Functions()

    Dim HighestScore As Integer
    Dim LowestScore As Integer

    Dim TopScore As Range
    Dim BottomScore As Range

    LowestScore = WorksheetFunction.Min(Range("B1", "B5"))
    HighestScore = WorksheetFunction.Max(Range("B1", "B5"))

    Set TopScore = Range("B1,B5").Find(What:=HighestScore)
    ' In the posted code was a typo. 
    ' Use Option Explicit to avoid these kind of errors.
    Set BottomScore = Range("B1,B5").Find(What:=LowestScore)

    Range("A7").Value = "lowest score is "
    Range("A8").Value = "Highest score is"

    ' Find method returned  a result only if BottomScore is not nothing
    If Not BottomScore Is Nothing Then
        ' In case the result is in the first row you can't go one row above
        If BottomScore.Row > 1 Then
            Range("B7").Value = BottomScore.Offset(-1, 0).Value
        End If
    End If

    ' The same if conditions as with Bottomscore
    If Not TopScore Is Nothing Then
        If TopScore.Row > 1 Then
            Range("B8").Value = TopScore.Offset(-1, 0).Value
        End If
    End If

End Sub