text search between workbooks, copy data

[G8834]

I have a workbook titled “data comparison” that contains sample i
numbers in column B ( example: Sample1, Sample10, sample32 etc.).
I would like to write code that opens a second workbook title
“results”, searches column A of that workbook for the matching sampl
ID (ex. Sample32), then copies data in the row with the matching sampl
ID and pastes the data back into the first workbook (data comparison).
Specifically, I am having trouble with the search method between tw
workbooks. Any tips would be helpful. Thank

 

[Mark Thorpe]

When dealing with two separate workbooks, you'll need to specify the
workbook in the code. I suggest creating two Workbook variables and using
them throughout. For example:

dim CompareWB as Workbook
dim ResultsWB as Workbook
dim SampleID as string
dim rFind as Range

set CompareWB=Workbooks("data comparison.xls")
set ResultsWB = Workbooks("results.xls")

rowcount = CompareWB.Worksheets(1).UsedRange.Rows.Count
for row = 1 to rowcount
SampleID = CompareWB.Worksheets(1).Cells(row,2).Value
set rFind = ResultsWB.Worksheets(1).Columns(1).Find(What:=SampleID,
LookAt:=xlWhole)
if not rFind is Nothing then
ResultsWB.Worksheets(1).Cells(rFind.Row,
2).Copy(CompareWB.Worksheets(1).Cells(row,3))
endif
next row