Hi Joe,

That's not exactly what I was trying to do. But we're on the right path:).
What I need to do is to look if on sheet1 for "a" in column A:A and if it
finds it take the value in the adjacent cell from the same row and put it in
the sheet2 in column "A". Let say A7. After that look for the next "a" in
sheet1 of range "A:A" and return the adjacent value in the same row
corresponding to the second "a".
I thing it should be used some loop here but I am not sure which one.

Thanks for your help

"Joe" wrote:

Hi lilianld

There are several solutions. One of them could be the following code...

Sub Test()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim rng As Range, rng1 As Range

' Origin
Set wks1 = Worksheets("Sheet1")
Set rng1 = wks1.Range("A:A")
' Destiny
Set wks2 = Worksheets("Sheet2")
' Searching
For Each rng In rng1
Select Case rng
Case "a", "b", "another_thing"
' put the value in the first column
' in the destination sheet
wks2.Cells(rng.Row, 1) = rng
Case Else
' nothing
End Select
Next

End Sub

Note 1: I used the SELECT CASE structure instead of IF...END IF because the
condition expression is simpler (using IF, we have to put several ORs to
complete the condition).

Note 2: In this solution I didn't want to use VLOOKUP. But it is possible.


I hope this could help you.

Bye

Joe




"lilianld" escreveu na mensagem
...
Hi,

It's my first post and I am a novice in VBA. I know my issue is simple one
but I cannot come out with the solution. Maybe some one can help.

What I am trying to accomplish is to search trough an entire column in the
first sheet1 (A:A) for a specific value (let say "a") and return the cell
value from column "C" of the same row in sheet2.

Ex:

Sheet1 Sheet2 (the same workbook)

A B C A B C

a 2 4 4
1 5 6
8 7
a 9 6

So in this case the result of the search is shown in Sheet2


I know that I can use the vlookup function but I need to look for more
than
one value in the column and return the cell values from each row.

Thanks in advance


.