Help with Excel.

[tamer64]

Hopefully I am in the right forum for this problem. I am starting a project in Excel and I need some help.

My project involves using a barcode scanner to scan boxes which contain product. For example and I will make this simple. The first barcode would scan as 1000 and the second would scan as 1005. I need to be able to display the set of numbers between the first scan and second scan.

A command button or maybe simply by scanning the starting and ending number would produce the desired results in excel.


Boxed
Inventory Numeric Scan
1000 1000
1001 1005
1002
1003
1004
1005

Could this be done. Your assistance is appreciated.

 

[mintjulep]

Look at the DataSeries method of the Range object.

 

[SkipVought]

What is the purpose of DISPLAYING these values?

What's the NEXT step in your process after these two scans?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tamer64]

The purpose is to have a print out of all the product that is contained in the a box. The box come to us barcoded with two barcodes affixed to the box. The first barcode contains the starting product number and the second barcode contains the last product number.

We may receive hundreds of boxes in one shipment. The excel spreadsheet would be essential in tracking down specific product numbers. Once a box is scanned on to a spreadsheet and the missing numbers populated it will be affixed to each box.

 

[SkipVought]

So do you have a table that includes the product number and all the other data you need?

You could very simply FILTER the table, using the two scanned values.

This should be really easy. So do you want to print each product number on a label?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tamer64]

I am not working with access this is an excel project.

The shipped packages (Boxed) will always contain the same type product. The amount of product in a box can be up to a quantity of 500. The outside of the box will have two barcodes assigned to it. The first barcode is the start of the first product number contained within the package. The second Barcode on the outside of the box is the product number of the last product number contained within the box. There could be a shipment of 100 boxes and no product number would be identical to another.

Box 1: Starting barcode: 1000 Ending Barcode: 1500.
Box 2: Starting barcode: 1501 Ending Barcode: 2001.

I need to be able to fill in the blanks. All numbers between 1000 and 1500 and the same for box 2. Ideally, If I were to scan box 1, I would need to populate the missing numbers onto an Excel spreadsheet.

 

[xlbo]

A starting point - it's not slick but it does the job

Public Sub Increment(sNum As Long, enNum As Long)
Dim sRow As Long

Dim ctr As Long

ctr = 0

sRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = sNum To enNum

Cells(sRow + ctr, "A") = i

ctr = ctr + 1

Next i

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tamer64]

Thank you for your assistance.

This is what I have setup so far and seems to do the job but I would like to modify it. This code will fill in the missing numbers but now, I have a noticed a problem with the way populates all the numbers into one column "C". I need to see if I can modify this code so that I can keep this all on one page.

For example Let's say if I had 200 product numbers. When I would run the code it would fill the product numbers in column "C1" with 1 through 50 and column "D1" with 51 through 101 and Column "E1" with 102 through 152 and so on...

Any Ideas...

[Blue]

Sub SearchProductNum()
Dim vStop, vStart
With WorksheetFunction
vStart = .Min(Sheet7.Range("A:B"))
vStop = .Max(Sheet7.Range("A:B"))
End With

With Sheet7
.Range("C1") = vStart
.Range("C1:C65536").DataSeries Step:=1, Stop:=vStop
End With
End Sub
[/blue]

 

[xlbo]

nope nope nope nope

Please do not do that

This is a common mistake when using excel - mixing of how data is stored vs how it is presented

Unless this is the last time you need to use this data and you are just putting it into excel to print it off and forget about it I would very strongly advise against this approach

Excel is very good at storing and presenting data - but you shouldn't try to do both things at once.

If you need to store this data for any kind of further use I would urge you to keep it in 1 column

Having said that, the following will do what you require:

Public Sub Increment(sNum As Long, enNum As Long, MaxRow as long)
Dim sRow As Long

Dim ctr As Long, colctr as long

rwctr = 0
colctr = 3

sRow = Cells(Rows.Count, colctr).End(xlUp).Row + 1

For i = sNum To enNum

if sRow + ctr > MaxRow then

colctr = colctr + 1

Cells(sRow + ctr, colctr) = i

ctr = ctr + 1

end if

Next i

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tamer64]

Thank You.

I will try this out and hopefully it will do what I need it to do.