Finding a value and filling in data

[Angie M.]

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.

 

[Tom Ogilvy]

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

 

[Angie M.]

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