VBA, Excel. How to Doubleclick a cell and have it popualate a number incrementally?

[TommyP]

Hello all,

I have an Excel problem that I need help with. Imagine a Fantasy Football Draft using Excel. You have a column of player names and a column for the "Pick Number" or the number that the player got chosen.

1 Adrian Peterson
2 Le'Veon Bell
4 Jamaal Charles
3 Eddie Lacy

How could I double click on a Pick Number cell and have it keep counting, incrementally by one, until the draft is over?


TommyP

 

[TommyP]

I was able to figure it out with the solution below. Hopefully someone will find it useful.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Range of Names
If Not Intersect(Target, Range("B2:B400")) Is Nothing Then
Cancel = True
'"N2" is a cell where I have an =MAX(A2:A400) formula to return the maximum number in the double clicked column.
Range(Target.Address).Value = Val(Range("N2").Value) + 1
End If
End Sub


TommyP