Generate list of number
 

Hi,

I want to generate a list in excel using a simple form.

-------------------------------------------------------
Start Range:<TEXT BOX
End Range:<TEXT BOX

<Generate button
-------------------------------------------------------
Start value: 7910050200300078970
End Value: 7910050200300078979

Upon generate, it should generate list like:

7910050200300078970
7910050200300078971
7910050200300078972
7910050200300078973
7910050200300078974
7910050200300078975
7910050200300078976
7910050200300078977
7910050200300078978
7910050200300078979
-------------------------------------------------------
After first list is generated in columnA, it should move on to next
blank column & generate list for new start/end values and so on.
Would appreciate an excel/VB form solution.

Generate list of number
 

Give this code a try in your Generate button's Click event...

Private Sub CommandButton1_Click()
Dim X As Long
Dim LastColumn As Long
Dim Number1 As Variant
Dim Number2 As Variant
Dim TBox1 As String
Dim TBox2 As String
TBox1 = TextBox1.Text
TBox2 = TextBox2.Text
If TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
Number1 = CDec(TBox1)
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
Number2 = CDec(TBox2)
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
For X = 0 To Number2 - Number1
Cells(X + 1, LastColumn + 1).Value = "'" & CStr(Number1 + X)
Next
Else
MsgBox "Bad entry in TextBox2"
End If
Else
MsgBox "Bad entry in TextBox1"
End If
End Sub

Rick


"Sinner" wrote in message
...
Hi,

I want to generate a list in excel using a simple form.

-------------------------------------------------------
Start Range:<TEXT BOX
End Range:<TEXT BOX

<Generate button
-------------------------------------------------------
Start value: 7910050200300078970
End Value: 7910050200300078979

Upon generate, it should generate list like:

7910050200300078970
7910050200300078971
7910050200300078972
7910050200300078973
7910050200300078974
7910050200300078975
7910050200300078976
7910050200300078977
7910050200300078978
7910050200300078979
-------------------------------------------------------
After first list is generated in columnA, it should move on to next
blank column & generate list for new start/end values and so on.
Would appreciate an excel/VB form solution.

Generate list of number
 

On Mar 13, 8:04*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Give this code a try in your Generate button's Click event...

Private Sub CommandButton1_Click()
* Dim X As Long
* Dim LastColumn As Long
* Dim Number1 As Variant
* Dim Number2 As Variant
* Dim TBox1 As String
* Dim TBox2 As String
* TBox1 = TextBox1.Text
* TBox2 = TextBox2.Text
* If TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
* * Number1 = CDec(TBox1)
* * If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
* * * Number2 = CDec(TBox2)
* * * LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
* * * For X = 0 To Number2 - Number1
* * * * Cells(X + 1, LastColumn + 1).Value = "'" & CStr(Number1 + X)
* * * Next
* * Else
* * * MsgBox "Bad entry in TextBox2"
* * End If
* Else
* * MsgBox "Bad entry in TextBox1"
* End If
End Sub

Rick

"Sinner" wrote in message

...



Hi,

I want to generate a list in excel using a simple form.

-------------------------------------------------------
Start Range:<TEXT BOX
End Range:<TEXT BOX

<Generate button
-------------------------------------------------------
Start value: 7910050200300078970
End Value: 7910050200300078979

Upon generate, it should generate list like:

7910050200300078970
7910050200300078971
7910050200300078972
7910050200300078973
7910050200300078974
7910050200300078975
7910050200300078976
7910050200300078977
7910050200300078978
7910050200300078979
-------------------------------------------------------
After first list is generated in columnA, it should move on to next
blank column & generate list for new start/end values and so on.
Would appreciate an excel/VB form solution.- Hide quoted text -

- Show quoted text -

Thx Rick.
Can we make the form float so that I can work on the sheet as well.
Along with error messages that you have put, if you can also include
messages for blank values like it should alert in case one or both the
text boxes are empty.

Thanks alot for your input. Appreciate tht : )

Generate list of number
 

On Mar 13, 2:48*pm, Sinner wrote:
On Mar 13, 8:04*am, "Rick Rothstein \(MVP - VB\)"





wrote:
Give this code a try in your Generate button's Click event...

Private Sub CommandButton1_Click()
* Dim X As Long
* Dim LastColumn As Long
* Dim Number1 As Variant
* Dim Number2 As Variant
* Dim TBox1 As String
* Dim TBox2 As String
* TBox1 = TextBox1.Text
* TBox2 = TextBox2.Text
* If TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
* * Number1 = CDec(TBox1)
* * If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
* * * Number2 = CDec(TBox2)
* * * LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
* * * For X = 0 To Number2 - Number1
* * * * Cells(X + 1, LastColumn + 1).Value = "'" & CStr(Number1 + X)
* * * Next
* * Else
* * * MsgBox "Bad entry in TextBox2"
* * End If
* Else
* * MsgBox "Bad entry in TextBox1"
* End If
End Sub

Rick

"Sinner" wrote in message

...

Hi,

I want to generate a list in excel using a simple form.

-------------------------------------------------------
Start Range:<TEXT BOX
End Range:<TEXT BOX

<Generate button
-------------------------------------------------------
Start value: 7910050200300078970
End Value: 7910050200300078979

Upon generate, it should generate list like:

7910050200300078970
7910050200300078971
7910050200300078972
7910050200300078973
7910050200300078974
7910050200300078975
7910050200300078976
7910050200300078977
7910050200300078978
7910050200300078979
-------------------------------------------------------
After first list is generated in columnA, it should move on to next
blank column & generate list for new start/end values and so on.
Would appreciate an excel/VB form solution.- Hide quoted text -

- Show quoted text -

Thx Rick.
Can we make the form float so that I can work on the sheet as well.
Along with error messages that you have put, if you can also include
messages for blank values like it should alert in case one or both the
text boxes are empty.

Thanks alot for your input. Appreciate tht : )- Hide quoted text -

- Show quoted text -

Also numbers starting with zero generate list without leading zero.

Start:09871270
End:09871277

Generates
9871270
9871271
9871272
9871273
9871274
9871275
9871276
9871277
Thx
.........

Generate list of number
 

See inline comments...

Can we make the form float so that I can work on the sheet as well.
Along with error messages that you have put, if you can also include
messages for blank values like it should alert in case one or both the
text boxes are empty.

Whatever code you are showing your UserForm from, use this statement to show
it instead of the statement you have now (assuming your UserForm is still
named UserForm1; if not, use your own UserForm's name instead... the key is
the vbModeless argument)...

UserForm1.Show vbModeless


Also numbers starting with zero generate list without leading zero.

Start: 09871270
End: 09871277

Give this new code a try...

Private Sub CommandButton1_Click()
Dim X As Long
Dim LastColumn As Long
Dim Number1 As Variant
Dim Number2 As Variant
Dim TBox1 As String
Dim TBox2 As String
TBox1 = Trim(TextBox1.Text)
TBox2 = Trim(TextBox2.Text)
If TBox1 = "" Or TBox2 = "" Then
MsgBox "You must fill in both text boxes!"
ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
Number1 = CDec(TBox1)
If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
Number2 = CDec(TBox2)
If Number2 < Number1 Then
MsgBox "TextBox2 must contain a larger number than TextBox1"
Else
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If LastColumn = 1 And Range("A1").Value = "" Then LastColumn = 0
For X = 0 To Number2 - Number1
Cells(X + 1, LastColumn + 1).Value = _
"'" & Format$(Number1 + X, String(Len(Trim(TBox1)), "0"))
Next
End If
Else
MsgBox "Bad entry in TextBox2"
End If
Else
MsgBox "Bad entry in TextBox1"
End If
End Sub

Rick

Generate list of number
 

On Mar 13, 7:00*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
See inline comments...

Can we make the form float so that I can work on the sheet as well.
Along with error messages that you have put, if you can also include
messages for blank values like it should alert in case one or both the
text boxes are empty.

Whatever code you are showing your UserForm from, use this statement to show
it instead of the statement you have now (assuming your UserForm is still
named UserForm1; if not, use your own UserForm's name instead... the key is
the vbModeless argument)...

UserForm1.Show vbModeless

Also numbers starting with zero generate list without leading zero.

Start: 09871270
End: 09871277

Give this new code a try...

Private Sub CommandButton1_Click()
* Dim X As Long
* Dim LastColumn As Long
* Dim Number1 As Variant
* Dim Number2 As Variant
* Dim TBox1 As String
* Dim TBox2 As String
* TBox1 = Trim(TextBox1.Text)
* TBox2 = Trim(TextBox2.Text)
* If TBox1 = "" Or TBox2 = "" Then
* * MsgBox "You must fill in both text boxes!"
* ElseIf TBox1 Like String(Len(TBox1), "#") And Len(TBox2) < 29 Then
* * Number1 = CDec(TBox1)
* * If TBox2 Like String(Len(TBox2), "#") And Len(TBox2) < 29 Then
* * * Number2 = CDec(TBox2)
* * * If Number2 < Number1 Then
* * * * MsgBox "TextBox2 must contain a larger number than TextBox1"
* * * Else
* * * * LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
* * * * If LastColumn = 1 And Range("A1").Value = "" Then LastColumn = 0
* * * * For X = 0 To Number2 - Number1
* * * * * Cells(X + 1, LastColumn + 1).Value = _
* * * * * * * * *"'" & Format$(Number1 + X, String(Len(Trim(TBox1)), "0"))
* * * * Next
* * * End If
* * Else
* * * MsgBox "Bad entry in TextBox2"
* * End If
* Else
* * MsgBox "Bad entry in TextBox1"
* End If
End Sub

Rick

Thanks for the input once again.
Worked like a charm.
Grateful dear.