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.

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." <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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." <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>