+ Reply to Thread
Results 1 to 3 of 3

Finding a value and filling in data

  1. #1
    Angie M.
    Guest

    Finding a value and filling in data

    Hello,

    I'm using Excel '03. Thanks for your help. I have a workbook that contains
    two sheets - Main and Scan.

    The Main sheet stores data on all employees (30,000 of them). Column A
    contains ID#s for all employees. Column B labeled Batch # and C is labeled
    Batch Date but these columns are blank. The rest of the columns contain
    names/addresses, etc.

    The Scan sheet contains 3 columns - ID#, Batch# and Batch Date to match the
    first 3 columns in the Main sheet. We are using a bar code scanner to scan
    in ID numbers from employee cards for the ID#, and the Batch # and Batch Date
    data is already filled in (via another macro). Since there are 25 employee
    cards scanned per batch, you end up with 25 rows of ID#, Batch # and Batch
    Date. The ID#s which are being scanned match an employee record somewhere on
    the Main sheet.

    Here's what I have to do: Add the batch# and batch date to the Main sheet
    for each ID number scanned in on the Scan sheet. So, I have to use the ID
    number on the scan sheet, match it to the same ID on the Main sheet and then
    fill in the batch # and batch date (which the user is prompted for).

    I've tried to use the macro recorder to activate the Scan sheet, copy the ID
    from the cell A2, pasted it into the Find box, then find the number on the
    Main sheet which works, but then I once I find the matching ID#, I need to
    enter the batch number and date to the two adjacent cells.

    In the end my Main sheet will show the batch # and batch date for each
    employee, but the preliminary step of having the Scan sheet accept the
    scanned bar codes is necessary. It would be fine to have a variable hold the
    batch # and batch date if that's a better way, this info does not need to be
    part of the Scan sheet which is a temporary holding place which is purged
    between batches.

    Thanks for any ideas.


  2. #2
    Tom Ogilvy
    Guest

    Re: Finding a value and filling in data

    Sub Process25()
    Dim rng1 as Range
    Dim rng3 as Range, cell as Range
    Dim res as Variant
    With Worksheets("Main")
    set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
    End with
    with Worksheets("Scan")
    for each cell in .Range("A2:A26")
    res = Application.Match(cell,rng1,0)
    if not iserror(res) then
    set rng3 = rng1(res)
    rng3.offset(0,1).Resize(1,2).Value =
    cell.offset(0,1).Resize(1,2).Value
    rng3.offset(0,2).NumberFormat = "mm/dd/yyyy"
    Else
    ' id not found, mark cell
    cell.Interior.ColorIndex = 3
    end if
    Next
    End With
    End Sub

    --
    Regards,
    Tom Ogilvy


    "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    news:9CF11024-B55A-4AC3-BA5C-BE36B7BB5980@microsoft.com...
    > Hello,
    >
    > I'm using Excel '03. Thanks for your help. I have a workbook that

    contains
    > two sheets - Main and Scan.
    >
    > The Main sheet stores data on all employees (30,000 of them). Column A
    > contains ID#s for all employees. Column B labeled Batch # and C is

    labeled
    > Batch Date but these columns are blank. The rest of the columns contain
    > names/addresses, etc.
    >
    > The Scan sheet contains 3 columns - ID#, Batch# and Batch Date to match

    the
    > first 3 columns in the Main sheet. We are using a bar code scanner to

    scan
    > in ID numbers from employee cards for the ID#, and the Batch # and Batch

    Date
    > data is already filled in (via another macro). Since there are 25

    employee
    > cards scanned per batch, you end up with 25 rows of ID#, Batch # and Batch
    > Date. The ID#s which are being scanned match an employee record somewhere

    on
    > the Main sheet.
    >
    > Here's what I have to do: Add the batch# and batch date to the Main sheet
    > for each ID number scanned in on the Scan sheet. So, I have to use the ID
    > number on the scan sheet, match it to the same ID on the Main sheet and

    then
    > fill in the batch # and batch date (which the user is prompted for).
    >
    > I've tried to use the macro recorder to activate the Scan sheet, copy the

    ID
    > from the cell A2, pasted it into the Find box, then find the number on the
    > Main sheet which works, but then I once I find the matching ID#, I need to
    > enter the batch number and date to the two adjacent cells.
    >
    > In the end my Main sheet will show the batch # and batch date for each
    > employee, but the preliminary step of having the Scan sheet accept the
    > scanned bar codes is necessary. It would be fine to have a variable hold

    the
    > batch # and batch date if that's a better way, this info does not need to

    be
    > part of the Scan sheet which is a temporary holding place which is purged
    > between batches.
    >
    > Thanks for any ideas.
    >




  3. #3
    Angie M.
    Guest

    Re: Finding a value and filling in data

    PERFECT! Thanks so much. It even marks the ID numbers that may be in error.
    Thank you for your help Tom!

    "Tom Ogilvy" wrote:

    > Sub Process25()
    > Dim rng1 as Range
    > Dim rng3 as Range, cell as Range
    > Dim res as Variant
    > With Worksheets("Main")
    > set rng1 = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
    > End with
    > with Worksheets("Scan")
    > for each cell in .Range("A2:A26")
    > res = Application.Match(cell,rng1,0)
    > if not iserror(res) then
    > set rng3 = rng1(res)
    > rng3.offset(0,1).Resize(1,2).Value =
    > cell.offset(0,1).Resize(1,2).Value
    > rng3.offset(0,2).NumberFormat = "mm/dd/yyyy"
    > Else
    > ' id not found, mark cell
    > cell.Interior.ColorIndex = 3
    > end if
    > Next
    > End With
    > End Sub
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Angie M." <AngieM@discussions.microsoft.com> wrote in message
    > news:9CF11024-B55A-4AC3-BA5C-BE36B7BB5980@microsoft.com...
    > > Hello,
    > >
    > > I'm using Excel '03. Thanks for your help. I have a workbook that

    > contains
    > > two sheets - Main and Scan.
    > >
    > > The Main sheet stores data on all employees (30,000 of them). Column A
    > > contains ID#s for all employees. Column B labeled Batch # and C is

    > labeled
    > > Batch Date but these columns are blank. The rest of the columns contain
    > > names/addresses, etc.
    > >
    > > The Scan sheet contains 3 columns - ID#, Batch# and Batch Date to match

    > the
    > > first 3 columns in the Main sheet. We are using a bar code scanner to

    > scan
    > > in ID numbers from employee cards for the ID#, and the Batch # and Batch

    > Date
    > > data is already filled in (via another macro). Since there are 25

    > employee
    > > cards scanned per batch, you end up with 25 rows of ID#, Batch # and Batch
    > > Date. The ID#s which are being scanned match an employee record somewhere

    > on
    > > the Main sheet.
    > >
    > > Here's what I have to do: Add the batch# and batch date to the Main sheet
    > > for each ID number scanned in on the Scan sheet. So, I have to use the ID
    > > number on the scan sheet, match it to the same ID on the Main sheet and

    > then
    > > fill in the batch # and batch date (which the user is prompted for).
    > >
    > > I've tried to use the macro recorder to activate the Scan sheet, copy the

    > ID
    > > from the cell A2, pasted it into the Find box, then find the number on the
    > > Main sheet which works, but then I once I find the matching ID#, I need to
    > > enter the batch number and date to the two adjacent cells.
    > >
    > > In the end my Main sheet will show the batch # and batch date for each
    > > employee, but the preliminary step of having the Scan sheet accept the
    > > scanned bar codes is necessary. It would be fine to have a variable hold

    > the
    > > batch # and batch date if that's a better way, this info does not need to

    > be
    > > part of the Scan sheet which is a temporary holding place which is purged
    > > between batches.
    > >
    > > Thanks for any ideas.
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1