Search & Fill

[kashyap]

I have some data something like below

<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu42</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu8</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfg</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">try</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">fngthj</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">htydfg</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">xyz</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfgo</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfglkn</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ergjjg</td> </tr> </tbody></table>
.. and search items something as below in a different sheet

<table width="64" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">xyz</td> </tr> </tbody></table>
trying to get output like below with macro

<table width="128" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">asd</td> <td style="width:48pt" width="64">asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu1</td> <td>asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu2</td> <td>asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu3</td> <td>asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu42</td> <td>asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">knbiu8</td> <td>asd</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">mno</td> <td>mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfg</td> <td>mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">try</td> <td>mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">fngthj</td> <td>mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">htydfg</td> <td>mno</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">xyz</td> <td>xyz</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfgo</td> <td>xyz</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">dfglkn</td> <td>xyz</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ergjjg</td> <td>xyz</td> </tr> </tbody></table>
can anyone help me with this pls..

Thank you..

 

[hiker95]

kashyap,


Sample worksheets before the macro:

Excel Workbook
	A
1	asd
2	mno
3	xyz
4
Sheet2

Excel Workbook
	A	B
1	asd
2	knbiu1
3	knbiu2
4	knbiu3
5	knbiu42
6	knbiu8
7	mno
8	dfg
9	try
10	fngthj
11	htydfg
12	xyz
13	dfgo
14	dfglkn
15	ergjjg
16
Sheet1

After the macro:

Excel Workbook
	A	B
1	asd	asd
2	knbiu1	asd
3	knbiu2	asd
4	knbiu3	asd
5	knbiu42	asd
6	knbiu8	asd
7	mno	mno
8	dfg	mno
9	try	mno
10	fngthj	mno
11	htydfg	mno
12	xyz	xyz
13	dfgo	xyz
14	dfglkn	xyz
15	ergjjg	xyz
16
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SearchAndFill()
' hiker95, 05/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=549261
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long, LR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
w1.Columns(2).ClearContents
For Each c In w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(1), 0)
  On Error GoTo 0
  If FR > 0 Then w1.Cells(FR, 2) = c
Next c
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("B1:B" & LR).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
With w1.Range("B1:B" & LR)
  .Value = .Value
End With
w1.Activate
Application.ScreenUpdating = True
End Sub

Then run the SearchAndFill macro.

 

[kashyap]

Thank you.. I was using loop function which was taking a long time..