Find Row from MAX expression

[Guest]

My objective is to select the row containing the highest value in a
particular column, copy the whole row and paste it in a different WS. I am
using this to find the MAX:

For i = 0 To varCount
varMaxSalary = Max(rngCell(1 + i, 29).Value, rngCell(2 + i,
29).Value)
Next i

...where varCount was earlier determined in a For Loop (SSNs), and rngCell is
selected in the outer For Loop..

What I think I want to do is add a line into this For Loop (or its
replacement) that defines varMaxSalaryRow. Problem is that varMaxSalary is a
Variant, not a Range, so I can't reference the Row from it as it is currently
defined.

Aaaaaargh!

Help, please?

 

[Guest]

This doesn't work either:

Set rngCheck1 = rngCell(1, 29)
Set rngCheck2 = rngCell(1 + varCount, 29)
varMaxSalary = Max(rngCheck1:rngCheck2)

rng items are Dim's as Range, of course, and var items as Variant.

 

[Guest]

Dim r as Range, res as Variant, r1 as Range

set r = rngCell.Resize(varcount + 1,1).offset(0,30)
varMaxSalary = Application.Max(varMaxSalary)
res = application.Match(varMaxSalary,r,0)
if not iserror(res) then
set r1 = r(res)
varMaxSalaryRow = r.row
else
varMaxSalaryRow = 1
End if

 

[Guest]

Waaal, shucks, I knew that (NOT!!!!!). Man do I have a lot to learn <g>.
However you will be pleased to know that I have just completed reading (and
downloading) Chip Pearson's two excellent treatises on using Variables.

Thanks a million for your help on this one!

 

[Guest]

Leetle TINY problem -- the code works like a champ, all but for one thing, it
is finding the Row with the smallest value in it rather than the greatest.
I've tried tweaking it several ways, but I'm stumped.

Any ideas? (TIA)

 

[Guest]

A couple of typos:

Sub ABC()

Dim r As Range, res As Variant, r1 As Range
Dim varMaxSalary As Double
Dim varMaxSalaryRow As Long
Set rngCell = Range("B2")
varcount = 100
Set r = rngCell.Resize(varcount + 1, 1).Offset(0, 28)
varMaxSalary = Application.Max(r)
res = Application.Match(varMaxSalary, r, 0)
Debug.Print res, r.Address, varMaxSalary
r.Select
If Not IsError(res) Then
Set r1 = r(res)
varMaxSalaryRow = r1.Row
Else
varMaxSalaryRow = 1
End If
Rows(varMaxSalaryRow).Select
End Sub

worked for me. To illustrate the locations I assumed (from the immediate
window):


Set rngCell = Range("B2")
varCount = 100
Set r = rngCell.Resize(varCount + 1, 1).Offset(0, 28)
? r.Address
$AD$2:$AD$102

If it doesn't work, then make sure it is looking at the correct range.

 

[Guest]

Thanks a million for your help. After I posted the question, and before I
received your reply, Main Momma, the Head Honchette, custodian of all
beneficence, showed up and kept me busy for an hour as she drove the main
task in another direction. However this code will still be used, and I am
most grateful for it.

In case you're wondering what's actually going on, the company where I am
currently Temping is moving its pension plan to a new Financial organization
(for the third time in ten years). On each previous move the data has had a
period of life in an Excel spreadsheet, and nobody has ever bothered to
standardize the spreadsheet format or content style <g>!

JOB SECURITY!!!! (For the Temp).