VBA help with an on the go formula for a group of cells gets highlighted.

jsrdgrace

New Member
Joined
May 5, 2015
Messages
2
Basics:
The column letter is static (something like column NX:NZ); the numbers of rows for a group can change. The math I want to perform would be positioned differently for each group.

Details:
I have data like the seen below – my sample shows three groups of various lengths, with one or more rows between separating the groups. The whole sheet is too large to sort based on Rank so as it sits now the top ranked score (#1) and second highest (#2) may be in different locations within each group in the spreadsheet.

There are formulas in the Score and Rank cells to show their values.

The Difference is always calculated within a group and placed in the row having the Rank of (#)1 and the calculation is simply the Score for item ranked 1 MINUS the Score for item ranked 2. There are occasional ties in ranks but those can be handled manually if the result shows an error or #N/A or whatever. I only evaluate for the difference between #1 and #2 scores.

What I would like to be able to do is highlight a the Rank column for a group (or Rank and Score areas of the spreadsheet), and have the macro evaluate and identify the Score where Rank=1 and Score where Rank=2, calculate the difference in Score, and put that in the appropriate cell in the right most column as shown below:

NX NY NZ
Score Rank Difference
76 3
56 4
89.5 1 3.5
86 2
33 5

56 4
58 3
59 2
60 1 1.0


63.3 1 2.3
45 6
57 4
58 3
44 7
61 2
52.5 5

Thanks for reviewing and helping. Please ask any questions you may have. I have Excel2013 and am a beginner VBA coder. I can decipher a reasonable amount of code but am not well versed in writing code from scratch.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi jsrdgrace,

On reading your request a second time, I may have mis-read your request... That being said, with the following code you can highlight either the first cell in your group or highlight the whole group (single col only - NX is your example) and the code will put the rank next to the score and calculate the difference to the right of rank #1. You can easily edit the code so that it does not write the rank to the sheet but continue to finding the difference between 1 and 2.


Code:
Sub RankCol()

    Dim rng As Range
    Dim rnk As Double
    Dim fr As Single
    Dim lr As Single
    Dim i As Single
    Dim col As Single
    Dim fir As Single
    Dim sec As Single
    Dim offs As Single

    Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
    col = rng.Column
    fr = rng.Row
    lr = rng.End(xlDown).Row
    
    For i = 0 To lr - fr
        rnk = WorksheetFunction.Rank(Cells(fr, col).Offset(i, 0), rng)
        ActiveCell.Offset(i, 1).Value = rnk
            If rnk = 1 Then fir = ActiveCell.Offset(i, 0).Value
            If rnk = 1 Then offs = i
            If rnk = 2 Then sec = ActiveCell.Offset(i, 0).Value
    Next
    
    ActiveCell.Offset(offs, 2).Value = fir - sec

End Sub


HTH

igold
 

jsrdgrace

New Member
Joined
May 5, 2015
Messages
2
igold - THANK YOU! THANK YOU! this works like a charm. Appreciate your swift reply to my post. This made my day! -jsrdgrace
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
jsrdgrace, Thank you for the feedback. I am happy that I was able to help you.

Regards,

igold
 

Watch MrExcel Video

Forum statistics

Threads
1,122,891
Messages
5,598,680
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top