PDA

View Full Version : Solved: Lookin for values between workbooks and get the details

sindhuja
07-28-2009, 01:58 PM
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
07-28-2009, 02:35 PM
Option Explicit
Sub Search()
Dim wsDyn As Worksheet
Dim wsStat As Worksheet
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)
i = 0
With wsStat.Columns(1)
Set c = .Find(cel, LookIn:=xlValues, lookat:=xlWhole, after:=.Cells(1000, 1))
If Not c Is Nothing Then

Do
i = i + 1
cel.Offset(, i) = c.Offset(, 1)
Set c = .FindNext(c)