Solved: Lookin for values between workbooks and get the details

[sindhuja]

Hello…

Workbook1 named Static wherein we have all the details (master sheet) and the other workbook named Dynamic wherein we copy paste the data daily (not same all the days)
Now what I want to do is I need to search all the values [column K of the Dynamic workbook (sheet1)] in column A of Static sheet and get the corresponding column B values in column L of Dynamic sheet. If more than one value in Static sheet then the corresponding value should be in the next column (column M of Dynamic sheet) and goes on…

Can anyone help me out in this pls..

-Sindhuja

[mdmackillop]

Option Explicit
Sub Search()
Dim wsDyn As Worksheet
Dim wsStat As Worksheet
Dim FirstAddress As String
Dim c As Range
Dim cel As Range
Dim i As Long

Set wsDyn = Workbooks("Dynamic.xls").Sheets(1)
Set wsStat = Workbooks("Static.xls").Sheets(1)
For Each cel In wsDyn.Columns(11).SpecialCells(xlCellTypeConstants)
FirstAddress = ""
i = 0
With wsStat.Columns(1)
Set c = .Find(cel, LookIn:=xlValues, lookat:=xlWhole, after:=.Cells(1000, 1))
If Not c Is Nothing Then

FirstAddress = c.Address
Do
i = i + 1
cel.Offset(, i) = c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
End Sub

[sindhuja]

Awesome.. you are rocking...

-sindhuja