Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Lookup Ques - finding value within a string to lookup | Excel Programming | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |