Supplementary questions from a part-time programmer who often gets stuck in
this area:

a) Why do you need to use the cell property of Range to assign a range to a
variable or to pase a range? For example rng1 = .Range("A1:C10") doesn't
work.

b) Why do you need to use 'Set' and not the implicit 'Let' in your
example?

Regards

Chris R. Lee


"Tom Ogilvy" a écrit dans le message de news:
...
Sub UpdateBook1()
Dim rng As Range
Dim sh As Worksheet
Dim rng1 As Range
Dim cell As Range
With Workbooks("Book1.xls").Worksheets("sheet1")
Set rng1 = .Range(.Cells(1, 2), _
.Cells(Rows.Count, 2).End(xlUp))
End With
For Each cell In rng1
For Each sh In Workbooks("Book2.xls").Worksheets
et rng = Nothing
Set rng = sh.Cells.Find(What:=cell, After:=sh.Range("65536"), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then Exit For
Next
If Not rng Is Nothing Then
rng1.Offset(0, 1).Value = rng.Offset(2, 0).Value
rng1.Offset(0, 2).Value = rng.Offset(1, 0).Value
rng1.Offset(0, 3).Value = rng.Offset(3, 0).Value
End If
Next


End Sub

would be a start.

--
Regards,
Tom Ogilvy

"ron_dallas" wrote in message
oups.com...
I have two workbooks,
book1 has col b with names, over 200 and growing.
book2 has multi sheets (different citys) with names, locations, ID,
other info

I need for ever name (200+ names) on book1
goto book2 find the name on any sheet
go down two cells and return value to book1 in col C
down one cell and return value to book1 in col D
down three cells and return vaule to book1 in col E

book1 before
ron
debbie

book1 after
ron dallas 5795664 on-site
debbie Austin 345923 tel-com

I know I am making this harder than it should be, but could use some
directions.

Thanks,
ron