Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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? -- Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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 -- Regards, Tom Ogilvy "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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! -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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) -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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) -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Row from MAX expression
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). -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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) -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dave Birley" wrote: 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? -- Dave Temping with Staffmark in Rock Hill, SC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
value expression | Excel Worksheet Functions | |||
"Find" expression won't work | Excel Programming | |||
need help with expression | Excel Discussion (Misc queries) | |||
Expression | New Users to Excel | |||
Expression for Macro - help | Excel Discussion (Misc queries) |