Resize VBA module

[accessabel]

Hi,
my company ships items in packs of 20 and only tracks them as a lot or 20pack serial number. Well that serial number is say xxx00 which expands to xxx01, 02, 03... up to xxx19. I have an excel VBA script that will do this function for me (insert new row, add +1, repeat up to x times). But I would much prefer to do this in Access as that would allow me to finish automating the process which involves many other steps.

I do not know how to do much in VBA through access. I tried to create a Class Module with the following but it tells me "user-defined type not defined" and highlights the "SUB" line at the beginning. I have no idea what to do next! Can anyone help? In the code Test is the table I want to pull from and [Lot No] is the column header.

Sub FillSerials()
Dim rng As Range

Set rng = Range("Test.[Lot No]")

While rng.Value <> ""
rng.Offset(1).Resize(19).EntireRow.Insert
rng.Resize(, 1).Copy rng.Resize(20)
If TypeName(rng.Value) <> "String" Then
rng.Resize(20).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Else
rng.AutoFill rng.Resize(20)
End If
Set rng = rng.Offset(20)
Wend

End Sub

 

[gemma-the-husky]

what are you trying to do exactly. track the batches of serials, or record each one individually? it isn't clear from the above

 

[accessabel]

Oh I am trying to make a list of numbers all ending in 00, 20, 40, 60 and 80 turn into 01, 02, 03... 19, 20, 21, 22, 23... 39, 40, 41, 42... etc. The code above also copied any cells next to the main list of numbers so that the shipping information would stay with the expanded list.

The above code worked in excel but I have to export the list of numbers, copy/paste the code into VB, then hit run, then import the expanded list of numbers back into access. I tried to run an excel macro from access but think that running the code from access might be easier.

So I mostly want to reuse the above code but I do not know what I need to do to make the code work. Maybe I need to 'class' it different, use public function... I'm pretty lost when it comes to VBA in access.

So it will start like this;
10000
10020
10040
10060

and will turn into this:
10000
10001
10002
10003
...
10019
10020
10021
10022
...
etc