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: