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

[jsrdgrace]

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.

 

[igold]

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.

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]

igold - THANK YOU! THANK YOU! this works like a charm. Appreciate your swift reply to my post. This made my day! -jsrdgrace

 

[igold]

jsrdgrace, Thank you for the feedback. I am happy that I was able to help you.

Regards,

igold