Double click to add number in cell above +1 in value

[Avalanchez]

Im looking for a way to when I put a number in say A1, any number at all, that when I click the cell below it it will add 1 to the value.

Example:
A1= 9547
i double click A2 and it now equals 9548

I know about auto fill and filling rows by dragging etc. I want this double click one at a time method though because for example the next number could skip and be 9550 but i still want to then go to 9551 after that.

Thanks for any help.

 

[lrobbo314]

How about this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Offset(-1).Value = Target.Offset(-1).Value + 1
Cancel = True
End Sub

 

[ZVI]

Hi,
Try this code in the sheet's VBA-module

' Put the below code into sheet's module:
' Copy this code - Right Click on the sheet's tab - View code - Paste the code - Alt-Q

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column <> 1 Or Target.Row = 1 Then Exit Sub
  Target.Value = Target.Offset(-1).Value + 1
  Cancel = True
End Sub

 

[Avalanchez]

How about this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Offset(-1).Value = Target.Offset(-1).Value + 1
Cancel = True
End Sub

that just seems to be changing the number in A1 when i click A2.

The number in A2 should be A1 +1

Feels like ur in the right direction thanks.

 

[Avalanchez]

Hi,
Try this code in the sheet's VBA-module

' Put the below code into sheet's module:
' Copy this code - Right Click on the sheet's tab - View code - Paste the code - Alt-Q

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column <> 1 Or Target.Row = 1 Then Exit Sub
  Target.Value = Target.Offset(-1).Value + 1
  Cancel = True
End Sub

Hey I think this works!, the only thing now is i have this code alreay in there for the date/ time how can i crunch this into there?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F1:G22")) Is Nothing Then
Cancel = True
Target.Formula = Time

End If
If Not Intersect(Target, Range("A1:A22")) Is Nothing Then
Cancel = True
Target.Formula = Date
End If

End Sub

Much appreciated

 

[Avalanchez]

Hey I think this works!, the only thing now is i have this code alreay in there for the date/ time how can i crunch this into there?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F1:G22")) Is Nothing Then
Cancel = True
Target.Formula = Time

End If
If Not Intersect(Target, Range("A1:A22")) Is Nothing Then
Cancel = True
Target.Formula = Date
End If

End Sub

Much appreciated

Also is there a way to make this column specific like i want column H and C to run the code you wrote only.

 

[ZVI]

Try this code then:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Not Intersect(Target, Range("F1:G22")) Is Nothing Then
    Cancel = True
    Target.Formula = Time
  End If
 
  If Not Intersect(Target, Range("A1:A22")) Is Nothing Then
    Cancel = True
    Target.Formula = Date
  End If
 
  If Target.Row = 1 Then Exit Sub
  ' The 3 and 8 are the columns numbers for C and H accordingly
  If Target.Column = 3 Or Target.Column = 8 Then
    Target.Value = Target.Offset(-1).Value + 1
    Cancel = True
  End If
 
End Sub

 

[ZVI]

Instead of the code line: If Target.Column = 3 Or Target.Column = 8 Then
you can use this one as well: If Not Intersect(Target, Range("C:C,H:H")) Is Nothing Then

 

[Avalanchez]

Try this code then:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Not Intersect(Target, Range("F1:G22")) Is Nothing Then
    Cancel = True
    Target.Formula = Time
  End If
 
  If Not Intersect(Target, Range("A1:A22")) Is Nothing Then
    Cancel = True
    Target.Formula = Date
  End If
 
  If Target.Row = 1 Then Exit Sub
  ' The 3 and 8 are the columns numbers for C and H accordingly
  If Target.Column = 3 Or Target.Column = 8 Then
    Target.Value = Target.Offset(-1).Value + 1
    Cancel = True
  End If
 
End Sub

That works perfect!

Thank you, you the man!

 

[ZVI]

That works perfect!

Thank you, you the man!

Glad it helped, welcome to MrExcel board!