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

<tbody>

</tbody>

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: