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

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

Close and thanks for the fast responce!!!!! :)

I had to change the find from sh.Range("65536") to ActiveCell, because
I got a "Run-time error '1004' Method 'Range' of object'_Worksheet'
failed, but the output puts the same info on every line.

It get the first city and puts it by every name, then gets the ID and
puts on every line and the same for location

Name Dallas 5795664 on-site
Ron Dallas 5795664 on-site
Debbie Dallas 5795664 on-site

Thanks, Tom I got it!!!!!!!!

I changed rng1 to cell it it works great now.

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

You use Set with objects (ranges, worksheets, workbooks, etc). You use Let (or
nothing) with simple variables (strings, Longs).

That way, VBA knows what you want....

Dim myStuff as Variant
let mystuff = range("a1:a10")
set mystuff = range("a1:A10")

The Let version will create an 10 row by 1 column array of the values in that
range (.value is the default property).

The Set version will assign that variable to that range--it includes all those
properties that are associated with ranges (borders, formulas, values, text,
font, ...)



"lee.christopher" wrote:

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




--

Dave Peterson