lookup
 

Hi,

I have worksheet with data as follows :


Col A Col B
ABC 123
ABC 234
ABC 345
AC 456
AC 567
AC 678


In Another Sheet I have
Col A
If the user inputs the ABC in cell A2 then
I need in Col D
123
234
345


I was using formula
=INDEX(Sheet1!$B$1:$B$10,SMALL(IF($A$2=Sheet1!$A$1 :$A$10,ROW(Sheet1!
$A
$1:$A$10),""),ROW(1:1)),0)


I am looking for VBA solution.

lookup
 

A little loop would help

Sub FindValue()


Dim r As Range, c As Range, s As Range
Dim ws As Worksheet, ws1 As Worksheet

Set ws = Worksheets("Sheet1")
Set ws1 = Worksheets("Sheet2")
Set r = ws.Range("A1", ws.Range("A65536").End(xlUp))
Set s = ws1.Range("A2")

For Each c In r.Cells
If c = s Then c.Offset(0, 1).Copy _
Destination:=ws1.Range("D65536").End(xlUp).Offset( 1, 0)
Next c

End Sub

lookup
 

This works for me

Sub tryme()
Worksheets("Sheet2").Columns("D:D").ClearContents
mytest = Worksheets("Sheet2").Range("A2")
k = 2
With Worksheets("Sheet1")
mylast = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 1 To mylast
If .Cells(j, 1) = mytest Then
Worksheets("Sheet2").Cells(k, "D") = .Cells(j, "B")
k = k + 1
End If
Next j
End With
End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hi,

I have worksheet with data as follows :


Col A Col B
ABC 123
ABC 234
ABC 345
AC 456
AC 567
AC 678


In Another Sheet I have
Col A
If the user inputs the ABC in cell A2 then
I need in Col D
123
234
345


I was using formula
=INDEX(Sheet1!$B$1:$B$10,SMALL(IF($A$2=Sheet1!$A$1 :$A$10,ROW(Sheet1!
$A
$1:$A$10),""),ROW(1:1)),0)


I am looking for VBA solution.