Increment Cell Range

mef1sto

Board Regular
Joined
Oct 31, 2013
Messages
85
Maybe someone could give me a helping hand with this code right here:

Rich (BB code):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("H13")) Is Nothing Then
      Run "testtwo"
  End If
end sub
'In a module
Sub testtwo()
Application.EnableEvents = False
Dim lastc As String, j, firstc As String, sstep
Dim msgTrap As VbMsgBoxResult
Dim newTP As Variant
   ':warning: SO I WANT TO INCREMENT THE DEFAULT NUMBER FROM RANGE B SO THAT THE CELL NUMBER INCREMENTS BY 1 IF THERE IS DATA IN THE LAST UNEMPTY ROW OF COLUMN B
'I FORGOT TO MENTION THAT THE FIRST CELL TO ENTER THE DATA IS RANGE("B16") AND THE END CELL IS RELATIVE
' SOMETHING LIKE IF IN CELL B18 I HAVE DATA THE NEXT TIME I DOUBLE CLICK RANGE h13 MY NEXT DEFAULT RANGE WOULD BE B19 AND NOT STILL b16 AS IN MY CODE RIGHT NOW....HOW DO I DO THAT?!
 msgTrap = MsgBox("Insert new data?", vbYesNo + vbQuestion, "?")
    Select Case msgTrap
    Case vbYes
    firstc = InputBox("Please insert the cell to store the data eg. B16", "!!!", Default:=Range("B16").Address(0, 0))
        If Len(firstc) = 0 Then Exit Sub
        newTP = InputBox("Insert the new data!", "Update!")
        Range(firstc) = newTP
        End If
        If newTP = 0 Then
            newTP = ""
            Range(firstc) = newTP
        End If
            Range("H13") = newTP 
    Case vbNo
        Exit Sub
    End Select
End Sub
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,214,834
Messages
6,121,874
Members
449,056
Latest member
ruhulaminappu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top