Posts by DataWarrior

DataWarrior · May 16th 2016

Re: Generate cells with values from other cells

I cant get it to work with the limited knowledge of VBA and macros I have. Any chance you can upload a working copy that works without having to press a button?
Reason: The PC will be across the room and I will be using a cordless barcode scanner.

DataWarrior · May 6th 2016

Re: Generate cells with values from other cells

As requested.
A1 and B1 are typed in, the desired outcome. A3 and B3 are what the scanner produces.

DataWarrior · May 6th 2016

Re: Generate cells with values from other cells

OK, there are multiple possibilities,
The device has 2 barcodes. 1 barcode has 9 numbers (123456789) and 1 has 2 digits of text, 9 numbers and 6 digits of text (AB123456789ABCDEF).
The 2 txt, 9 num, 6 txt is consistent everywhere except the one on the device.
I just need a way to eliminate the text from being entered in the cells.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa
If Target.Address = "$B$1" Then
            Dim startNum As Double
    Dim numLoops As Long, c As Long, nextRow As Long
    nextRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
    If nextRow = 2 Then nextRow = 2
    startNum = Range("A1").Value
    numLoops = Range("B1").Value - startNum
    Application.ScreenUpdating = False
    For c = 0 To numLoops
        Cells(nextRow + c, "C").Value = c + startNum
    Next c
    Application.ScreenUpdating = True
    End If
    Application.EnableEvents = False
    If Not Target.Cells.CountLarge > 1 Then
        If Not Intersect(Target, Columns(1)) Is Nothing Then
            Target.Offset(, 1).Select
        ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
            Target.Offset(-0, -1).Select
        End If
    End If
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Display More

DataWarrior · May 6th 2016

Re: Generate cells with values from other cells

I'll do as requested tomorrow to make absolutely sure I got everything correct.

DataWarrior · May 5th 2016

Re: Generate cells with values from other cells

There will always be 2 leading letters, 9 numbers that increment by 1 with 4 items per box and 5 trailing letters.
The reason for the adjustment is there are 2 barcodes on the device, 1 is only numbers and 1 has the letter, number, letter combination.
Because of the type barcode my scanner can't scan the whole barcode and eliminate the letters. I've already contacted the scanner mfgr.
The box they are shipped in has the number, letter, number combination and everything slows down if I have to open each box.
If possible I need the letters removed, prevented or eliminated without user intervention.

DataWarrior · May 4th 2016

Re: Generate cells with values from other cells

Concerning this same thread;
I have a barcode with letters then numbers then letters. Example 'AA123456789BBCC'.
How can I cause Excel 2010 to prevent the text characters from being entered in a cell without using a macro or popup?
I want Excel to completely ignore anything except numbers.
No Msg box. No having to click a command button, simply ignore the unwanted characters.
Can your result be incorporated into my current code?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
If Target.Address = "$B$1" Then
Dim startNum As Double
Dim numLoops As Long, c As Long, nextRow As Long
nextRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
If nextRow = 2 Then nextRow = 2
startNum = Range("A1").Value
numLoops = Range("B1").Value - startNum
Application.ScreenUpdating = False
For c = 0 To numLoops
Cells(nextRow + c, "C").Value = c + startNum
Next c
Application.ScreenUpdating = True
End If
Application.EnableEvents = False
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Columns(1)) Is Nothing Then
Target.Offset(, 1).Select
ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
Target.Offset(-0, -1).Select
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub

DataWarrior · May 3rd 2016

Re: Generate cells with values from other cells

So grateful for your help and pointers.
I was able to add the Macro code to the Private Sub. So using a Bluetooth scanner with the cursor in A1 the user can scan the first number,
the cursor then moves to B1, they scan the second number, the macro runs and cursor moves back to A1.
I'll look into some error checking that makes sure A1 is populated before moving to B1.

Can't say it enough, Thanks for your help.

DataWarrior · May 2nd 2016

Re: Generate cells with values from other cells

This is perfect. I made a minor change so the S/N's starts in C2.
I set it so the cursor moves from A1 to B1 and back to A1 on change, froze the top line and gave C1 a header of Serial Number and added a button to run the macro.
Still wish the macro would run when cell B1 was changed. Oh well. Can't have everything.
Thank you so very much for the help.

VBA to add Serial Numbers

Sub SerialNumbers()
Dim startNum As Double
Dim numLoops As Long, c As Long, nextRow As Long
nextRow = Cells(Rows.Count, "C").End(xlUp).Row + 1
If nextRow = 2 Then nextRow = 2
startNum = Range("A1").Value
numLoops = Range("B1").Value - startNum
Application.ScreenUpdating = False
For c = 0 To numLoops
Cells(nextRow + c, "C").Value = c + startNum
Next c
Application.ScreenUpdating = True
End Sub

VBA to move from A1 to B1 and back if there are any changes.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Columns(1)) Is Nothing Then
Target.Offset(, 1).Select
ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
Target.Offset(-0, -1).Select
End If
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub

DataWarrior · May 1st 2016

Re: Generate cells with values from other cells

Better explanation,
When the Serial Numbers macro runs the first time it creates the serial numbers but when it runs the second time it writes over the first set of numbers.
I need it to for (lack of a better word), append the results to the end of the first set of numbers and do the same each time it runs.
Each time the macro runs I need it to append the results to the end of the last set of numbers.
I can create the macro button, hadn't thought of that.

DataWarrior · May 1st 2016

Re: Generate cells with values from other cells

Thanks,
2 things
1) Can it be set to calculate automatically. Maybe because of where I pasted the code but I have to press F8 to run the macro.
2) I need to keep each calculation, I have a pallet of 92 items that may or may not be in sequence so I may be scanning several times.
And can it be set so the cursor goes back to A1 after a value is entered in A2 so the person scanning wont have to go back to the keyboard after scanning each number?

DataWarrior · Apr 30th 2016

Hello everyone,
I'm trying to streamline my inventory process.
I get 'widgets' in a box of 4 on a pallet of 96. Each widget has a unique serial number. There is a barcode on the box with the S/N of each item in the box. Same for the pallet.

I want to scan or type the first S/N in cell A1 (1),
the last S/N in cell A2 (96),
Cell A3 calculates the difference (96) and populates cell B1 starting at 1 (the value of A1) to 96 (the value of A2)
I should have 96 S/N's in column B Starting at 1,2,3 - 96.
I need to do this multiple times and not overwrite any other cells.
I'm not locked in on which cells any of the data has to be in.