VBA to create

[manc]

Dear Demigods,

Picture the scene.... it was a dark night,
A1 = "How many labels", B1 = user unput.
Then an ActiveX button

User clicks the button and in the dark alleyway, behind the metal dustbins, 'it' generates a list of sequential numbers, starting from the last saved number. The workbook saves, and closes.

I can't even think of a description to put in google :D :D :D

Any help is greatly appreciated!

Best regards
manc

 

[Logit]

'it' generates a list of sequential numbers, starting from the last saved number.

How many numbers ?

 

[manc]

How many numbers ?

5 numbers, starting at 20000

 

[Logit]

Place your cursor in the beginning cell. Make certain the next 4 rows below this cell are clear.

Paste this macro in a regular module :

Option Explicit

Sub foo()
Dim i As Integer
Dim ii As Integer

    For ii = 20000 To 20005
        ActiveCell.Value = ii
            ActiveCell.Offset(1, 0).Select
    Next ii

End Sub

 

[manc]

Place your cursor in the beginning cell. Make certain the next 4 rows below this cell are clear.

Paste this macro in a regular module :

Option Explicit

Sub foo()
Dim i As Integer
Dim ii As Integer

    For ii = 20000 To 20005
        ActiveCell.Value = ii
            ActiveCell.Offset(1, 0).Select
    Next ii

End Sub

Hi Logit,

Apologies - i mis-understood your question asking how many numbers...

The number starts at 20000, then the increment increases by the user input in cell B1.
for example, user selects 10 in B1

Sequence generates as follows
20000
20001
20002
20003
20004
20005
20006
20007
20008
20009
Ideally, the seuquence should appear on another sheet.
The workbook is saved and closed.
When it is re-opened, the number starts at 20010 and waits for the next user input in B1.

Trust this explains it better than I did previous.

Best regards
manc

 

[Alex Blakenburg]

Macros need more specific instructions then what you have here.
What is the name of the sheet you want the data on ?
What Cell do you want the sequence to start at ?
When the numbers keep going are they appended to the existing numbers or do the existing numbers need to be deleted ?
If deleted is there data next to the numbers and are we deleting the whole row ?
In your image you have the data input in C1 but your text says B1, which is correct ?

 

[manc]

Macros need more specific instructions then what you have here.
What is the name of the sheet you want the data on ?
What Cell do you want the sequence to start at ?
When the numbers keep going are they appended to the existing numbers or do the existing numbers need to be deleted ?
If deleted is there data next to the numbers and are we deleting the whole row ?
In your image you have the data input in C1 but your text says B1, which is correct ?

Assuming that we have no data entered thus far:
Sheet2!A1:A - contains a list of sequential numbers, starting from 20000 in cell A1, to last row in ColA
Sheet1!A1 - requires user to enter the number of labels they wish to print (let's say 10 in this example)
Sheet1!B1 - requires user to enter a delivery number - always 4 digits (let's say 4433 in this example)
They then click the button
On button click:
Sheet3!A1:A - is where the sequence of numbers is temporarily pasted in ColA.
On exit:
Sheet2!B1:B - VBA finds the last empty row in ColB and pastes the 4 digit delivery number from Sheet1!B1 in the next 'n' rows, depending on value in Sheet1!A1
Workbook is saved and exits.

On restart, the user wants to print another 5 labels - delivery ID will be 4456. The sequence of numbers in ColA should now start from the adjacent LastRow ColB+1, i.e. 20010



Based on the above and after running the macro for the first time with a value of '10' entered in cell Sheet1!A1, I would expect to see on Sheet2!A1:B:

ColA 20000 20001 20002 20003 20004 20005 20006 20007 20008 20009

ColB 4455 4455 4455 4455 4455 4455 4455 4455 4455 4455

Running it for a second time, I would expect to see on Sheet2!A1:B:

ColA 20000 20001 20002 20003 20004 20005 20006 20007 20008 20009 20010 20011 20012 20013 20014

ColB 4455 4455 4455 4455 4455 4455 4455 4455 4455 4455 4456 4456 4456 4456 4456

And so forth, and so forth

Trust ok. Please help

Best regards
manc

 

[Alex Blakenburg]

Give this a try on a copy or your workbook.

Sub CreateDeliveryRegister()

    Dim wbThis As Workbook
    Dim inputSht As Worksheet, regSht As Worksheet, delivSht As Worksheet
    Dim delivRng As Range, regRng As Range
    Dim NoOfLabels As Long, DelivNo As Long, NextItemNo As Long, ItemNoStopNo As Long
    Dim regLRow As Long
    
    Set wbThis = ThisWorkbook
    Set inputSht = Worksheets("Sheet1")
    Set regSht = Worksheets("Sheet2")
    Set delivSht = Worksheets("Sheet3")
        
    With inputSht
        NoOfLabels = .Range("A1")
        DelivNo = .Range("B1")
    End With
    
    With regSht
        regLRow = .Range("A" & Rows.Count).End(xlUp).Row
        NextItemNo = .Range("A" & regLRow).Value + 1
        ItemNoStopNo = NextItemNo + NoOfLabels - 1
    End With
    
    Set delivRng = delivSht.Range("A1")
    With delivRng
        .CurrentRegion.Clear
        .Value = NextItemNo
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
                    Step:=1, Stop:=ItemNoStopNo, Trend:=False
    End With
    
    Set delivRng = delivRng.CurrentRegion
    Set regRng = regSht.Range("A" & regLRow + 1).Resize(delivRng.Rows.Count)
    With regRng
        regRng.Value = delivRng.Value
        regRng.Offset(0, 1) = DelivNo
    End With
    
    With wbThis
        .Save
        .Close
    End With
    
End Sub

 

[manc]

Alex, wow.

Wasn't expecting the full code but many many thanks for your solution. Does exactly what i envisaged.

Genuinely, really appreciate you giving up your time.

Best regards
manc

 

[Alex Blakenburg]

You're welcome. Glad we could help.