A project assigned to me

[marty1975]

Hi, all. I am a novice at VBA but I am trying to create my own little excel spreadsheet for work and I could use some help.

This spreadsheet would keep track of the daily lab specimens in four racks. Each day of the week has four racks that sit in our fridge. For example, each rack would have, A1-12, B1-12, C1-12, D1-12, and, E1-12.

For example:

Rack1
A1 08-15-278-0019
A2 08-15-278-0020
A3 08-15-278-0021
A4 08-15-278-0022
...
A12 08-15-278-0124

and so on...

Rack2
A1 08-15-278-0101
A2 08-15-278-0079
A3 08-15-278-0156
A4 08-15-278-0025
...
A12 08-15-278-0235

and so on...

I would scan the barcode into the table and the barcode would be assigned to 'A1'. The programming would tell me where to put the next specimen. In this case, the next spot would be 'A2' if it is not already taken.

My question is, how do I assign the barcode number that I scan in to the already assigned cell number? For example, in Rack1, the barcode 08-15-278-0019 would be assigned to A1.

The reason why I want it this way is that the staff should be able to search the table at a later time and find that particular barcode 08-15-278-0019 is in A1.

Attached is what I have so far.

Thanks for your help.

Marty

[SamT]

This spreadsheet would keep track of the daily lab specimens in four racks. Each day of the week has four racks that sit in our fridge

I would use a different sheet for each day of the week, Does the fridge have a shelf for each day of the week?

On each Day Sheet, I would have four tables, one for each Rack. The tables should be shaped like the Racks

In Module TableDesriptions, Create constants that define each Table

Const Rack1Address As String = "B4:F7"
Const Rack2Address As String = "B10:F13"

In Code you can access each individual rack with

Set ActiveRack = Sheets("Monday").Range(Rack2Address)

For Inserts you can find the first Rack with an empty space by looping thru the tables on the sheet and

IF WorksheetFunction.CountA(ActiveRack) < 20 Then
For Each Cel in ActiveRack
If Cel = "" Then Cel =Barcode
Exit For
End If

For Finding a barcode, use

For each Sht in DaySheets
Set RackSlot = Find Barcode
For each ActiveRack
If Not Intersect(RackSlot, ActiveRack) Is Nothing Then
Use some simple math to find the actual rack space Barcode is in

The InsertFrm and FindFrm should return the Shelf, Rack Number and Space to the User