CODEWITHSUNDEEP
excelvba
Sax Max
Sax Max

Reputation: 1

Issues with .Find function returning errors when searching for a maximum value in a range

I am trying to find and define a row which describes the maximum value in a range. The range is a column of numbers with decimal points which is imported into the sheet. I can find the max value but the .Find function is returning "object variable or with block variable not set" when I try and define the row it is in.

I know that this error is returned if the value is not found. MsgBox dMax just prior to the .Find operation shows that it has found the max value for the range.

The data set is a series of long positive and negative non integer numbers, e.g. -0.594396474479286. I've ensured that the data format is "general".

'finding highpoint

Dim dMax As Double
Dim HighPoint As Long

   With ActiveSheet.Range("A2:A10000")
        dMax = WorksheetFunction.Max(.Range("A2:A10000"))

        MsgBox dMax

        If dMax > 0 Then

         HighPoint = Range("A2:A10000").Find(What:=dMax, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row


        End If
    End With

Can anyone point to what I have done wrong here? It seems like the .find function can't find the max value..

Upvotes: 0

Views: 57

Answers (2)

DanStu
DanStu

Reputation: 174

You are referencing an "After" value outside your search range. You can simply delete this named parameter, or change it to 

HighPoint = Range("A2:A10000").Find(What:=dMax, After:=Cells(2, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row

Upvotes: 1

user11053804
user11053804

Reputation:

Use the worksheet's Match function to locate the row containing the maximum number.

Dim dMax As Double
Dim HighPoint As Long

With ActiveSheet.Range("A:A")

    dMax = application.Max(.cells)
    MsgBox dMax

    If dMax > 0 Then

        HighPoint = application.Match(application.Max(.cells), .cells, 0)

    End If
End With

debug.print HighPoint

Your own code may be suffering from a floating point decimal problem.

Watch your use of With, End With blocks. .Range("A2:A10000") within ActiveSheet.Range("A2:A10000") is actually .Range("A3:A10001") on the worksheet and you were missing the prefix . in the second use of Range("A2:A10000").

Upvotes: 1

Related Questions