Generate list of number

[Sinner]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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










- 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 : )

 

[Sinner]

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
.........

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Sinner]

See inline comments...


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



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.