I would like to define some specific id numbers in VBA/Macro. When the user types in a message box a specific number 100 and the length of the number 11. The outcome should be 100,101,...,110. Really appreciate.
Expected Result=SEQUENCE(11,1,1000000,1)
The idGenerator prompts the user to type the base and the counter separated by one of [,./ -]. If the input is not valid reprompt until the user escape or type valid numbers. The function returns an 1D array with the IDs. After as you can see in my example we can write the array in any position in a sheet horizontally or vertically
Sub testGenerator()
Dim rslt As Variant
rslt = idGenerator()
If LBound(rslt) = 0 Then Exit Sub
With Application.WorksheetFunction
'IF YOU WANT TO COPY THE ARRAY IN ROWS
Range("H2").Resize(UBound(rslt), 1) = .Transpose(rslt)
'IF YOU WANT TO COPY THE ARRAY IN COLUMNS
Range("I2").Resize(1, UBound(rslt)) = rslt
End With
End Sub
Public Function idGenerator() As Variant()
Dim answ As Variant, c As Long, ch As String, p As Long, gtmp As String, ctmp As String
Dim idBase As Long, counter As Long, rslt() As Variant
Const separ = "-./, "
Const msg = "Please type the ID base and the count" & vbCrLf & "separated by space or one of: / , . -"
'if lbound(idGenerator) eq 0 => no user input
ReDim idGenerator(0 To 0)
Linput:
answ = Trim(InputBox(msg, "ID GENERATOR"))
If answ = "" Then Exit Function
For c = Len(separ) To 1 Step -1
ch = Mid$(separ, c, 1)
p = InStr(1, answ, ch)
If p > 0 Then
GoTo Lgen
End If
Next
Call MsgBox(msg, vbCritical)
GoTo Linput
Lgen:
gtmp = Trim(Left(answ, p - 1))
ctmp = Trim(Mid$(answ, p + 1))
If Not (IsNumeric(gtmp) And IsNumeric(ctmp)) Then
MsgBox ("Please for Id and count type only mumbers" & vbCrLf & "separated by space or one of: / , . -")
GoTo Linput
End If
idBase = CLng(gtmp)
counter = CLng(ctmp)
ReDim rslt(1 To counter)
For c = 1 To counter
rslt(c) = idBase + c - 1
Next
idGenerator = rslt
End Function
WorksheetFunction.Sequenceyet, as I prompted to your previous question? It's not clear where you want the outcome btw.InputBoxorApplication.InputBox). Please describe in more detail the whole process you have in mind, from getting the first, then the second number, to the cells (e.g. a column or row starting with the selected (active) cell) where the result should be written. Add the information to your post.