Previous Topic Next Topic

Retrive Value to empty cell

Solved/Closed
Ornitor - May 3, 2012 at 09:10 AM
 Ornitor - May 4, 2012 at 03:00 AM
Hello,

Hope you can help me...

The user select a value from a list (in Column B), then Excel need to find this value from a column (Column D) on another sheet, and return the corresponding value from Column C, into the original cell.

So... Sheet2 (Cell C2), need to get value from looking at the value in Sheet2 (Cell B2), then find that exact value in Sheet 1 (ColumnD), and terun the value adjacent to Sheet 1 (Column D) which is Sheet 1 (Column A), and the v alue in Sheet 1 Column A needs to be copied and pasted to Cell C2 (Sheet2)

3 replies

Answer 1 / 3
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
May 3, 2012 at 09:41 AM
Hi Ornitor,

If it's possible to move (or copy) Sheet1 ColumnA to Sheet1 ColumnE you could use VLOOKUP:

Sheet2 Cell C2: =VLOOKUP(B2,Sheet1!D$2:E$10,2)

If ColumnE is already used, you can use another column as long as it's to the right of ColumnD.

Best regards,
Trowa
0
Answer 2 / 3
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 510
May 3, 2012 at 10:15 AM
Since I had some spare time, here is a code you might like: 

Sub RetrieveValue()
Dim lRow, lRow2, x As Integer
lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet2").Range("B2:B" & lRow)
x = 1
    Do Until x = lRow2
    x = x + 1
    If cell.Value = Sheets("Sheet1").Range("D" & x) Then _
    cell.Offset(0, 1).Value = Sheets("Sheet1").Range("A" & x).Value
    Loop
Next cell
End Sub

Best regards,
Trowa
0
Answer 3 / 3
Ornitor
May 4, 2012 at 03:00 AM
The VLookup works..NICE!
0