Pull highest score from Range

Jerk24

Board Regular
Joined
Oct 10, 2012
Messages
190
Good afternoon

I have a sheet (have been here before modifying it) for my dart league and I want to pull the highest game from a range of number inside a cell

So below is a snap shot of where the data is input. each Wk will have the equation something like =14+23+25 (different for all shooter, obviously) for each game.

I already have a code running to pull the highest game over all for each shoot (also included in post) now all i want is the highest game the last week shot (not sure if this is editable to pick up indivdual vaules. =LOOKUP(2,1/(1-ISBLANK(C3:K3)),C3:K3)
not sure if a Formula will work

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long, Pluses As String, Numbers As Variant, Rng As Range
Const SetOffsets As Long = 4
For x = 0 To 147
If Not Intersect(Target, Range("C3:K3,C5:K5").Offset(x * SetOffsets)) Is Nothing Then
Set Rng = Range("C3:K3,C5:K5").Offset(x * SetOffsets)
GoTo Continue
End If
Next
Exit Sub
Continue:
With WorksheetFunction
Pluses = .Trim(Join(.Index(Rng.Areas(1).Formula, 1, 0), " ")) & _
"+" & .Trim(Join(.Index(Rng.Areas(2).Formula, 1, 0), " "))
Pluses = Replace(.Trim(Replace(Replace(Pluses, "+", " "), "=", " ")), "ABS", 0, , , vbTextCompare)
Cells(Rng(1).Row + 2, "O").Value = Evaluate("MAX(" & Replace(Pluses, " ", ",") & ")")
End With
End Sub


hope this is enough information




Wk 1
Wk 2
Wk 3
Wk 4
Wk 5
Wk 6
Wk 7
Wk 8
Wk 9
67
ABS
63
68
56
ABS
77
58
65
Wk10
Wk11
Wk12
Wk13
Wk14
Wk15
Wk 16
Wk 17
Wk 18





<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jerk24

Board Regular
Joined
Oct 10, 2012
Messages
190
reading through this again. I will need the single highest game in Column Y.

thanks -
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Try:
Code:
Sub Test()
    Application.ScreenUpdating = False
    Dim lastCell As Range
    Dim x As Long
    Dim rng As Range
    Dim pts1 As String
    Dim pts2 As String
    Dim pts3 As String
    Dim pts4 As String
    Dim LastRow As Long
    LastRow = 193
    For x = 3 To LastRow Step 4
        Set rng = Application.Union(Range("C" & x & ":K" & x), Range("C" & x + 2 & ":K" & x + 2))
        Set lastCell = rng.Find(What:="*", After:=Range("C" & x), _
        LookAt:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
        On Error Resume Next
        pts4 = Split(lastCell.Formula, "+")(0)
        pts2 = Split(lastCell.Formula, "+")(1)
        pts3 = Split(lastCell.Formula, "+")(2)
        pts1 = Right(pts4, Len(pts4) - 1)
        Range("Z1") = pts4
        Range("Z2") = pts2
        Range("Z3") = pts3
        If lastCell = "ABS" Then
            Range("V" & x - 1) = 0
        Else
            Range("V" & x - 1) = WorksheetFunction.Max(Range("Z1:Z3"))
        End If
        Range("Z1:Z3").ClearContents
    Next x
    Application.ScreenUpdating = True
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238

ADVERTISEMENT

My pleasure. :)
 

Jerk24

Board Regular
Joined
Oct 10, 2012
Messages
190
Is there a way to make this automatic, I have been messing around trying to add it to the current event code I have, no dice.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
The macro that I suggested has to be placed in a regular module and run separately. It cannot work as a Worksheet_Change event because the total scores for each week in your worksheet are returned by a formula. A change in a cell that is returned by a formula will not trigger a Worksheet_Change macro so it must be run manually.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,947
Members
416,144
Latest member
JohnMacDonald

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