Number input increment by one is it possible

[Wayne_Sadler]

Hi all

I am not sure if this can be done but I'll explain

The numbers starting vary ie 45986 or 50984 so the aim would be to be able to click on a cell and enter a start fig then it counts 45986 then 45987 and so on incrementing by 1 with a end number or none.

Can anyone please advise me how I can do this or is there a script I can put into a macro to do the same job.

Many thanks everyone that can help me

W Sadler

 

[Scott Huish]

Sub test()
Dim sn As Long, en As Long
Range("A:A").ClearContents
sn = Application.InputBox("Enter starting number", Type:=1)
en = Application.InputBox("Enter ending number", Type:=1)
Range("A1") = sn
Range("A:A").DataSeries stop:=en
End Sub

 

[Wayne_Sadler]

Hi HOTPEPPER

That's great, if does the trick...
Please forgive me as my knowledge of excel is limited...

I tried to change the cell part "Range("A1") = sn" A1 to say A2 and it would not work is there any reason for this. Since I am not sure on what cell I'll be starting from.

I am very grateful of your help.

Wayne

 

[Jameo]

Not quite sure why the above is not working, maybe HOTPEPPER can explain when he is back. Another way to do this would be:

Sub test()
Dim sn As Long, en As Long
Range("A:A").ClearContents
sn = Application.InputBox("Enter starting number", Type:=1)
en = Application.InputBox("Enter ending number", Type:=1)
Dim IntRow As Long
IntRow = 2
Do
Cells(IntRow, 1) = sn
IntRow = IntRow + 1
sn = sn + 1
Loop Until sn = en + 1
 

End Sub

If you need to change the starting position, for the row index change IntRow = 2, for the column index change the highlighted 1 in Cells(IntRow, 1) = sn

i.e to start in cell D5 it would be IntRow = 5, Cells(IntRow, 4) = sn

Good luck.

 

[repairman615]

How will you know in what cell to start?

You may be able to programatically sort out where to start. You would need to give details to the logic.

Here is an adaptation of HOTPEPPERS code that includes a third Inputbox used for a cell location. This human interface will allow you to specify what cell to start with.
No quotes are needed when using.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> sn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, en <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngCadr <SPAN style="color:#00007F">As</SPAN> Range<br>Range("A:A").ClearContents<br>sn = Application.InputBox("Enter starting number", Type:=1)<br>en = Application.InputBox("Enter ending number", Type:=1)<br><SPAN style="color:#00007F">Set</SPAN> rngCadr = Application.InputBox("Enter top cell address", Type:=8)<br>rngCadr = sn<br>Range(rngCadr.Address & ":A" & Rows.Count).DataSeries stop:=en<br><SPAN style="color:#007F00">'''Thanks HOTPEPPER for the UCASE tip, I use it all the time _<br>                                                            Repairman615</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Scott Huish]

A small adaptation:

Sub test()
Dim sn As Long, en As Long
Dim rngCadr As Range
Range("A:A").ClearContents
sn = Application.InputBox("Enter starting number", Type:=1)
en = Application.InputBox("Enter ending number", Type:=1)
Set rngCadr = Application.InputBox("Enter top cell address", Type:=8)
rngCadr = sn
rngCadr.Resize(en - sn + 1).DataSeries
End Sub

 

[Wayne_Sadler]

Big Thank you to HOTPEPPER, Jameo & Repairman615 in helping me out on this.

Wayne Sadler