Using VBA to find the address of maximum in a range containing formulae

[Jon EvanCook]

Excel 2007 VBA
I have a spreadsheet which currently uses the database functions and VBA to to select specific rows of the 'Database' area("A7:A48") and store them as a continuous block of data in the 'Extract' area("U7:AM48"). In this case the data is stored as values and I have no problems identifying the row containing the last data set using xlup etc.

I have now changed the spreadsheet to use formulae to extract the data rows. I am now having problems trying to find a simple method to find the last row of the extracted data set as instead of blank cells in the extract area, all the cells now contain formulae and xlup only finds the last formula. The last row is easily identified as each row of the data set contains a unique sequential number in increasing values(range Z8:Z48) so the last row always contains the maximum value.

Test routine - this differs slightly from my main procedure because the workbook contains several sheets which can call the same macro and which stores the sheet name in a string variable called 'mainsheet'.

Sub MaxValRow()

Dim MaxVal As Double
Dim Myrange As Range, TgtRng As Range
Dim FinSeq As Range

Set Myrange = ActiveWorkbook.Sheets("4").Range("Z8:Z48") 'selection
MaxVal = Application.Max(Myrange)

Set TgtRng = Myrange.Find(what:=MaxVal)
FinSeq = TgtRng.Row

MsgBox "Maximum is in Row " & FinSeq

End Sub

I get a Run-time error '91' message saying 'Object variable or With block variable not set.' which I cannot identify. I'm sure the solution must be simple but...

The Target Range Z8:Z48 contains numbers in this case Z8 formula returns the value 1 and Z9 contains the result 2 and MaxVal is equal to 2 but it appears the line 'FinSeq = TgtRng.Row' finds no row to return.

Jon

 

[Akuini]

Because of Dim FinSeq As Range, it should be:
Dim FinSeq As Long

 

[steve the fish]

You dimension FinSeq as a range so this line:

FinSeq = TgtRng.Row

has to fail. Firstly you have to Set objects like ranges and secondly TgtRng.Row isnt a range but an integer.

You could say:

Set FinSeq = TgtRng
MsgBox "Maximum is in Row " & FinSeq.Row

or dimension FinSeq as Long.

 

[Jon EvanCook]

Of course... what an idiot! I meant to Dim FinSeq as Long and did not look back after I wrote the Dim statements

Many thanks
Jon

 

[Jon EvanCook]

I spoke too soon, correcting the Dim Statement makes the routine run successfully but the Row number returned is incorrect, as noted in my original post the maximum value of '2' is in cell Z9 of the given range, whereas the corrected routine returns the value '12' not '9'.

The Formulae in "Z8:Z48" are VLookUps and the maximum value shown in "Z9" is 2, however VLookup is finding that value from Row 12 and I assume it is not coincidence that the macro is showing that FinSeq is equal to 12? I am a bit confused as to why the macro is giving the originating row of the Vlookup("F12") and not the row of the result cell "Z9"?

 

[Fluff]

How about just using

FinSeq = ActiveWorkbook.Sheets("4").Range("Z8:Z48").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row

 

[Jon EvanCook]

How about just using

FinSeq = ActiveWorkbook.Sheets("4").Range("Z8:Z48").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row

This worked a treat, I have not seen that 'Find("*",....' trick before, but it looks like the simplest solution to my problem.

Cheers

 

[Fluff]

You're welcome & thanks for the feedback.

The "trick" with that is the xlValues part which will ignore any formula that returns ""