Increment numbers in column A by 1 when selected

[mikeburg]

Need code to increment a number from the cell above in column A when column A cell is selected. Have tried using the following code but the number does not increment by 1, it merely copies the number from the cell above.

(1) Why does the following code not work?

(2) What would be the modification to make the following code work or what would be the simple code to take the number from above in column A, add 1, & put it in the selected cell of column A if the selected cell is blank, empty, or -0-?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
R = Target.Row
If Target.Row > 3 And Target.Column = 1 And Cells(R, 1) = 0 _
And Cells((R - 1), 1).Value > 0 Then
Cells(R, 1).Value = Cells((R - 1), 1).Value + 1
End If
End Sub

Thanks a million. mikeburg

[Ardus Petus]

Your code generates an error if you select A1

'----------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If .Row > 3 And .Column = 1 Then
If .Value = 0 And .Offset(-1, 0).Value > 0 Then
.Value = .Offset(-1, 0).Value + 1
End If
End If
End With
End Sub
'-----------

"mikeburg" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Need code to increment a number from the cell above in column A when
> column A cell is selected. Have tried using the following code but the
> number does not increment by 1, it merely copies the number from the
> cell above.
>
> (1) Why does the following code not work?
>
> (2) What would be the modification to make the following code work or
> what would be the simple code to take the number from above in column
> A, add 1, & put it in the selected cell of column A if the selected
> cell is blank, empty, or -0-?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> R = Target.Row
> If Target.Row > 3 And Target.Column = 1 And Cells(R, 1) = 0 _
> And Cells((R - 1), 1).Value > 0 Then
> Cells(R, 1).Value = Cells((R - 1), 1).Value + 1
> End If
> End Sub
>
> Thanks a million. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=536399
>

[Tom Ogilvy]

The code worked fine for me.

--
Regards,
Tom Ogilvy


"mikeburg" wrote:

>
> Need code to increment a number from the cell above in column A when
> column A cell is selected. Have tried using the following code but the
> number does not increment by 1, it merely copies the number from the
> cell above.
>
> (1) Why does the following code not work?
>
> (2) What would be the modification to make the following code work or
> what would be the simple code to take the number from above in column
> A, add 1, & put it in the selected cell of column A if the selected
> cell is blank, empty, or -0-?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> R = Target.Row
> If Target.Row > 3 And Target.Column = 1 And Cells(R, 1) = 0 _
> And Cells((R - 1), 1).Value > 0 Then
> Cells(R, 1).Value = Cells((R - 1), 1).Value + 1
> End If
> End Sub
>
> Thanks a million. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=536399
>
>

[Ardus Petus]

If Target.Row > 3 And Target.Column = 1 And Cells(R, 1) = 0 _
And Cells((R - 1), 1).Value > 0 Then

Cells((R-1),1) errs if R = 1

--
AP

"Tom Ogilvy" <[email protected]> a écrit dans le message
de news:[email protected]...
> The code worked fine for me.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "mikeburg" wrote:
>
> >
> > Need code to increment a number from the cell above in column A when
> > column A cell is selected. Have tried using the following code but the
> > number does not increment by 1, it merely copies the number from the
> > cell above.
> >
> > (1) Why does the following code not work?
> >
> > (2) What would be the modification to make the following code work or
> > what would be the simple code to take the number from above in column
> > A, add 1, & put it in the selected cell of column A if the selected
> > cell is blank, empty, or -0-?
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > R = Target.Row
> > If Target.Row > 3 And Target.Column = 1 And Cells(R, 1) = 0 _
> > And Cells((R - 1), 1).Value > 0 Then
> > Cells(R, 1).Value = Cells((R - 1), 1).Value + 1
> > End If
> > End Sub
> >
> > Thanks a million. mikeburg
> >
> >
> > --
> > mikeburg
> > ------------------------------------------------------------------------
> > mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> > View this thread:
http://www.excelforum.com/showthread...hreadid=536399
> >
> >

[mikeburg]

Thanks a million. mikeburg