BEBaldauf

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

ASKER

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

findMaxPercent-r2.xls

ASKER

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

fixMaxPercent-r3.xls

ASKER

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)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

ASKER

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

findMaxPercent-r11.xls

ASKER

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

ASKER

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

findMaxPercent-r1.xls