asked on # Do a calculation on only 1 row of a group of rows

As part of a larger macro, I am selecting a group of rows based on a certain value. I need to select only one to run a calculation on.

-Find rows that have identical CombinedSection numbers

-Find the row with the largest FullValue

-Copy that FullValue to the Percent cell only on that row

Here is what it should end up like:

Class CombinedSection Instructor FullValue Percent

12345 0450-0001 Smith, Joe 10.00 10.00

12346 0450-0001 Smith, Joe 9.00

12347 0450-0001 Smith, Joe 8.00

-Find rows that have identical CombinedSection numbers

-Find the row with the largest FullValue

-Copy that FullValue to the Percent cell only on that row

Here is what it should end up like:

Class CombinedSection Instructor FullValue Percent

12345 0450-0001 Smith, Joe 10.00 10.00

12346 0450-0001 Smith, Joe 9.00

12347 0450-0001 Smith, Joe 8.00

Microsoft Excel

Hi Dave,

I'm handling all the calculations through a series of macros, because there are so many of them with lots of nested If..Then scenarios - this being one of them.

Any way to turn this into VBA?

Thanks,

Bethyn

I'm handling all the calculations through a series of macros, because there are so many of them with lots of nested If..Then scenarios - this being one of them.

Any way to turn this into VBA?

Thanks,

Bethyn

Ok.

See attached.

Dave

findMaxPercent-r2.xls

```
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
Set rng = wks.Range("A2", wks.Range("A" & wks.Rows.Count).End(xlUp))
Set rng = rng.Offset(, 4).Resize(, 1)
rng.Cells(1, 1).FormulaArray = "=IF(MAX(($D$2:$D$100)*($B$2:$B$100=B2))=$D2,$D2,"""")"
rng.FillDown
rng.Value = rng.Value
End Sub
```

See attached.

Dave

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Could a For...Next loop be used so a formula wouldn't have to be written into the cell?

It is a bit more sophisticated logic to figure this out without the formula. Perhaps a for next loop to paste the formula one at a time, then clear it? That should still be MUCH faster than doing the logic the formula is doing via VBA.

Your call.

Thoughts?

Dave

Your call.

Thoughts?

Dave

Here's a solution without using the formula at all. With the formulaic approach, using a loop, only one iteration would be required.

See attached.

Dave

fixMaxPercent-r3.xls

```
Option Explicit
Sub updatePercent()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Dim myDict As Object
Set wkb = ThisWorkbook
Set wks = wkb.ActiveSheet
Set myDict = CreateObject("Scripting.Dictionary")
Set rng = wks.Range("B2", wks.Range("B" & wks.Rows.Count).End(xlUp))
For Each r In rng
If myDict.exists(r.Value) Then
If myDict(r.Value) < r.Offset(, 2).Value Then
myDict(r.Value) = r.Offset(, 2).Value
End If
Else
myDict.Add r.Value, r.Offset(, 2).Value
End If
Next r
For Each r In rng
If myDict(r.Value) = r.Offset(, 2).Value Then
r.Offset(, 3).Value = r.Offset(, 2).Value
End If
Next r
myDict.RemoveAll
Set myDict = Nothing
End Sub
```

See attached.

Dave

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Thanks, i will give that a try. May take me a little time to implement.

What does the Offset do? (just so I grasp a little of what you did)

What does the Offset do? (just so I grasp a little of what you did)

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Offset is moving so many rows or columns from a starting point.

r.Offset(,4) would be equivalent to for columns to the right of r, same rows.

Dave

r.Offset(,4) would be equivalent to for columns to the right of r, same rows.

Dave

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

You might try ensuring that your formula is array-entered. Go to the formula, hit F2, then CRTL+SHIFT+ENTER.

Also, it looks like you eliminated a column, and shifted this dataset around from being something that starts in column A, so ensure you have your formula correct.

e.g., It should be

[AP2]=IF(MAX(($AN$2:$AN$100)*($AJ$2:$AJ$100=AJ2))=$AN2,$AN2,"")

CTRL+SHIFT+ENTER to confirm.

Why are you not using the macro, now? PS - uploading a sample is better than having me type all this in to update/correct your formula, lol :P

See attached, formula based approach.

Dave

findMaxPercent-r11.xls

Also, it looks like you eliminated a column, and shifted this dataset around from being something that starts in column A, so ensure you have your formula correct.

e.g., It should be

[AP2]=IF(MAX(($AN$2:$AN$10

CTRL+SHIFT+ENTER to confirm.

Why are you not using the macro, now? PS - uploading a sample is better than having me type all this in to update/correct your formula, lol :P

See attached, formula based approach.

Dave

sorry, when I couldn't quite get the macro working, i thought i'd try just the formula. And you are right, it's the array issue with the formula so now that works. This will help me work on either the macro or the formula now, so i think i'm good to go! appreciate your assistance!

Do you want me to update the macro to work with those columns, or are you good to go?

Dave

Dave

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

think i'm good, thanks!!!

However, wouldn't you like to just use a formula, instead?

If your data starts in A2, and goes to row 100 (you can increase that to a larger number, if needed to support the # rows you have in the worksheet) you can put the Percent value in column E, e.g.:

[E2]=IF(MAX(($D$2:$D$100)*

And hit CTRL+SHIFT+ENTER to confirm the array formula. You can edit the formula after you paste it in, by hitting F2, then hit CTRL+SHIFT+ENTER to confirm.

Then copy down.

See attached.

Cheers,

Dave