| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 : ) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ......... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to generate a list of prime number? | Excel Discussion (Misc queries) | |||
| How generate new list in another worksheet from existing list? | Excel Worksheet Functions | |||
| Generate list | Excel Worksheet Functions | |||
| Generate random number from a list | Excel Worksheet Functions | |||
| generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
Linear Mode
