Generate cells with values from other cells

  • 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.

  • Re: Generate cells with values from other cells


    Try this. :cool:

    Bruce :cool:

  • 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?

  • Re: Generate cells with values from other cells


    You should have a menu where you can look at the list of macros available to you.


    Maybe google your version of Excel with the word macros.


    Example:
    Running macros in Excel 2007 etc.
    You could also draw a shape or button and link it to the macro.


    As far as your second request I don't understand what you want, upload a sample.


    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

    Bruce :cool:

  • 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.

  • Re: Generate cells with values from other cells


    Maybe this. :cool:


    If you were to enter 100 and 105 run the macro, then you enter 200 to 205 and run the macro then 200 to 205 ends up under 100 to 105.


    If you clear out all the data and start over the data once again starts in B1.


    Bruce :cool:

  • 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

  • Re: Generate cells with values from other cells


    You can run it when B1 changes, but the issue is you need to make sure the user changes A1 first since B1 will trigger the code which will run with whatever numbers are in place.

    Bruce :cool:

  • 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.

  • 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

  • Re: Generate cells with values from other cells


    Well the simple answer is if your data really is formatted as AA123456789BBCC as you have indicated then you can use the mid function, but if your example is simply made up and there could be more or less letter etc. then I need a better example and I need to know if the numbers of letter and digits can change.


    Saying something like A235, but then saying later it could be two letters before or no letters before or no letters at all or letters then numbers then letters etc. doesn't help me get you to a solution.

    Bruce :cool:

  • 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.

  • Re: Generate cells with values from other cells


    Why don't you post a small workbook with a sample of what the scanner would put into the spreadsheet and what you would like the results to be.


    Please use code tags when posting code.


    On the toolbar there is a # icon. If you click on that you will get two of those icons and if you copy you code and put the cursor between those icons and paste your code in the middle then when you save the post your code will be formatted like mine.

    For uploading a workbook:

    When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

    Bruce :cool:

  • 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.

    Files

    • Widgets.xlsm

      (47.63 kB, downloaded 69 times, last: )
  • Re: Generate cells with values from other cells


    If you want help please don't give me what you think I need, give me what I ask for.


    Quote

    Why don't you post a small workbook with a sample of what the scanner would put into the spreadsheet and what you would like the results to be.

    Bruce :cool:

  • Re: Generate cells with values from other cells


    You don't have to use the code as is, it just gives you an example of how to do what you want.


    First thing is we use isnumeric to see if the data is a number or not.


    If it has text in it as you indicated then it will return false.


    So if it's a number we just let the start number equal the value in the cell, otherwise we use the mid function which needs to know where you want to start and how many characters you want to pull from the string.


    We say start at character 3 and give us 9 total characters.


    Just strip out what you need from my code or write some code into your code based on your understanding of how this works.

    Code
    1. Sub DetermineStartNum()
    2. Dim startNum As Double
    3. If IsNumeric(Range("A3").Value) Then
    4. startNum = Range("A3").Value
    5. Else
    6. startNum = Mid(Range("A3").Value, 3, 9)
    7. End If
    8. End Sub

    Bruce :cool:

  • 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.