CODEWITHSUNDEEP
excelvba
Live2Win
Live2Win

Reputation: 13

Find row number of the value that is found by 'Find' function

Here is my code, trying to find the row number of the value that is found by Find() previously.

Dim rng as range
Dim fndrng as range

Set rng = Range(cells(row1, col), Cells(row2, col))
max_value = Application.WorksheetFunction.Max(rng) 
    
Set fndrng = rng.find(what:=max_value, LookIn:=xlValues)
answer = fndrng.row

My code doesn't work, but instead print the error saying, "Run-time error '91': Object variable or with block variable not set"

It seems to me that fndrng is always empty after running the line<rng.find(what:=max_value, LookIn:=xlValues)>. However, if I manually use 'find' function in the worksheet by 'ctrl+F', it can find the value(s)!

Is there anyone who can help me out here? I have no idea how come it isn't working. It would be appreciated if you can share better alternatives when it comes to getting row number of the value I am looking for.

Thanks.

Upvotes: 0

Views: 411

Answers (4)

Live2Win
Live2Win

Reputation: 13

For those of you who has been struggling to find the solution to the same problem as I did and finally got to here, I figured out that the data type of 'max_value' needs to be 'variant' in this case, and it was 'long' at first.

This is how I solved out this question, and I hope this can help anyone who suffers.

Upvotes: 0

Wito
Wito

Reputation: 303

With the shared code, I believe you could have an issue with the following line:

Set rng = Range(cells(row1, col), Cells(row2, col)

You did not share what kind of object are row1, row2 and col in your code. Therefore, I'm going to make assumptions for the code you shared to work, declaring those object as Long:

Dim row1 As Long
Dim row2 As Long
Dim col As Long

row1 = 1
row2 = 20
col = 1

Set rng = Range(cells(row1, col), Cells(row2, col))

This would make rng to be equivalent to "A1:A20" You can of course modify this to your desired range, and maybe add a col1 and col2 instead of a single column to expand the range. We now look for the max_value in the "A1:A20" range. Below the test code I used and the simulation in my worksheet:

Sub test()
Dim rng As Range
Dim fndrng As Range

Dim row1 As Long
Dim row2 As Long
Dim col As Long

row1 = 1
row2 = 20
col = 1

Set rng = Range(Cells(row1, col), Cells(row2, col))

max_value = Application.WorksheetFunction.Max(rng)
        
Set fndrng = rng.Find(what:=max_value, LookIn:=xlValues)
answer = fndrng.row

MsgBox answer

End Sub

Below the values in my worksheet, the max_value would be 100 located in row 9

enter image description here

The MsgBox received:

enter image description here

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36840

You need to define rng as Range type. Try below sub then adjust the code to your own.

Sub FindMaxValueRow()
Dim rng As Range

    With Worksheets(1).Range("A1:A500")
        Set rng = .Find(2, LookIn:=xlValues)
        If Not rng Is Nothing Then
            MsgBox rng.Row
        End If
    End With
    
End Sub

To search max value use this line Set rng = .Find(max_value, LookIn:=xlValues)

Source of code to Microsoft Range.Find

Upvotes: 0

Ray
Ray

Reputation: 21

I'm assuming it's not somethings as simple as DTLC_max vs max_value

Upvotes: 0

Related Questions