Retriving value from multiple sources

[Stephen]

Hi Folks,

I have two workbooks (wb1, wb2). wb1 looks like this

A B
2 CAT1
cust1
cust2...
5 CAT2
custA
custB...
12 CAT3
cust1a
cust1b...

wb2 looks like...

A B C D
2 849 cust1 2849
2 341 cust2 2341
5 89 custA 589
5 93 custB 593
12 91 cust1a 1291
12 429 cust1b 12429

I'm trying to get values from wb2 col D into wb1 colA (in blank cells),
where the match is the CAT value in wb1 col A and the first ten charactors in
wb1 col B.

the result I am looking for in wb1 is...

A B
2 CAT1
2849 cust1
2341 cust2...
5 CAT2
589 custA
593 custB...
12 CAT3
1291 cust1a
12429 cust1b...

I have no idea how to accomplish this. PELASE HELP!!!!!

 

[Joel]

Sub finddata()
Set wb1 = Workbooks("wb1.xls")
Set wb2 = Workbooks("wb2.xls")

With wb1.Sheets("Sheet1")
RowCount = 1
Do While .Range("B" & RowCount) <> ""
If .Range("A" & RowCount) = "" Then
customer = .Range("B" & RowCount)
With wb2.Sheets("Sheet1")
Set c = .Columns("C").Find(what:=customer, _
LookIn:=xlValues)
If Not c Is Nothing Then
wb1.Sheets("Sheet1").Range("A" & RowCount) = _
c.Offset(0, 1)
End If
End With
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[Stephen]

with a little massaging this worked GREAT!!!

thank you kindly.