# Pull highest score from Range

#### Jerk24

##### Board Regular
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:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Jerk24

##### Board Regular
reading through this again. I will need the single highest game in Column Y.

thanks -

#### mumps

##### Well-known Member
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``````

#### Jerk24

##### Board Regular
this works like a charm. Thank you very much.

My pleasure.

#### Jerk24

##### Board Regular
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,533
Messages
5,854,292
Members
431,636
Latest member
shabbas313

### 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.

### Which adblocker are you using?

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

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