Mikebe
05-21-2018, 06:51 AM
Hi,
I have to keep track of the location of batches of certificates. To do this I want a spreadsheet which will show the 3 letter prefix in column A, and the first certificate number of each pack within the batch in column B.
Cell H1 contains the number of certificates in each pack
So far I have:
Sub AddNewPacks()
Dim Amount As String, Prefix As String, Firstno As String, Counter As Integer, Thisgo As Integer
Prefix = InputBox("Please enter the Prefix of the certificates being added")
Amount = InputBox("Please enter the number of packs to be added")
If Amount = "" Then Exit Sub
If Amount > 1000 Then
MsgBox "Invalid Amount. Number of packs cannot be more than 1000"
Exit Sub
End If
For Thisgo = 1 To Amount
Range("A1").End(xlDown).Offset(1, 0).Value = Prefix
Next Thisgo
Firstno = InputBox("Please enter the low st serial number")
If Firstno > 999999 Then
MsgBox "Invalid Serial Number."
Exit Sub
End If
Range("B1").End(xlDown).Offset(1, 0).Value = Firstno
For Counter = 1 To Amount
ActiveCell.Offset(1, 0).Value = ActiveCell + H1
Next Counter
End Sub
This puts the prefix in A the correct number of times, but I can't get the serial number part to work.
Any help would be appreciated
I have to keep track of the location of batches of certificates. To do this I want a spreadsheet which will show the 3 letter prefix in column A, and the first certificate number of each pack within the batch in column B.
Cell H1 contains the number of certificates in each pack
So far I have:
Sub AddNewPacks()
Dim Amount As String, Prefix As String, Firstno As String, Counter As Integer, Thisgo As Integer
Prefix = InputBox("Please enter the Prefix of the certificates being added")
Amount = InputBox("Please enter the number of packs to be added")
If Amount = "" Then Exit Sub
If Amount > 1000 Then
MsgBox "Invalid Amount. Number of packs cannot be more than 1000"
Exit Sub
End If
For Thisgo = 1 To Amount
Range("A1").End(xlDown).Offset(1, 0).Value = Prefix
Next Thisgo
Firstno = InputBox("Please enter the low st serial number")
If Firstno > 999999 Then
MsgBox "Invalid Serial Number."
Exit Sub
End If
Range("B1").End(xlDown).Offset(1, 0).Value = Firstno
For Counter = 1 To Amount
ActiveCell.Offset(1, 0).Value = ActiveCell + H1
Next Counter
End Sub
This puts the prefix in A the correct number of times, but I can't get the serial number part to work.
Any help would be appreciated