Solved: Vlookup Help

[jumbel]

I have two worksheets given as below.

Page 1

1 24
2 36
3 5
4 3
5 5
6 4
7 5
8 8
9 7
11 5

Page 2

11
9
8
7
6
5
4
3
2
1

Now, with a macro, I need to display all the corresponding values from page1. I like to display all the values with a macro.

[mdmackillop]

Sub Lookup()
Dim cel As Range, c As Range
With Sheets(2)
For Each cel In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set c = Sheets(1).Columns(1).Find(cel, lookat:=xlWhole)
If Not c Is Nothing Then
cel.Offset(, 1) = c.Offset(, 1)
End If
Next
End With
End Sub

[jumbel]

works awesome....thnks a lot mac!!

Can you please explain this line of code

cel.Offset(, 1) = c.Offset(, 1)

Also, how can i access the values if the page 2 is in another workbook???

[mdmackillop]

Cel.Offset(,1) means the cell one column to the right of the range defined by "cel". You can also say .Offset(0,1), but 0 is the default value and need not be entered.

For another workbook.
Sub Lookup()
Dim cel As Range, c As Range
Dim WB As Workbook
'Assumes workbook is open
Set WB = Workbooks("Book1.xls")
With Sheets(1)
For Each cel In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set c = WB.Sheets(1).Columns(1).Find(cel, lookat:=xlWhole)
If Not c Is Nothing Then
cel.Offset(, 1) = c.Offset(, 1)
End If
Next
End With
End Sub

[Krishna Kumar]

Another way..

Sub kTest()
Dim r1 As Range, r2 As Range, s As String
Set r1 = Sheets(1).Range("a1").CurrentRegion.Resize(, 2)
Set r2 = Sheets(2).Range("a1").CurrentRegion.Resize(, 1).Offset(, 1)
s = "'" & Sheets(1).Name & "'!" & r1.Address(ReferenceStyle:=xlR1C1)
With r2
.FormulaR1C1 = "=VLOOKUP(RC[-1]," & s & ",2,0)"
.Value = .Value
End With
End Sub

HTH

[mdmackillop]

Hi Krishna,
With that method, I would add a SpecialCells line to remove the error cells where there is no VLOOKUP return.
Regards
MD

[Krishna Kumar]

Hi MD,

Good point.

[jumbel]

I am still unable to get it :(..

I have two columns J and K in the sheet 2 of workbook1 and two columns K and HY in workbook2.
Column J in workbook1 and column K in workbook2 have the same values, but not in same order. Now I need to lookup for values and display the matching HY cloumn values in column K in workbook1.

P.S: Workbook2 may or may not be open.

[mdmackillop]

Add a line to open Workbook 2 at the start and close it at the end. Try the macro recorder for this code.

[jumbel]

thanks to both Mac and Krishna for ur help!! :)