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:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

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
Top