On 19 Feb, 02:24, lilianld wrote:
Hi Joe,

I would appreciate a lot if you would help me finalize my small project.

Thanks



"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

.- Nascondi testo citato

- Mostra testo citato -

Hi Lilialnd.
Try with a little change to the Joe's routine.

Sub Test()
Dim wks1 As Worksheet, wks2 As Worksheet
Dim rng As Range, rng1 As Range
' Origin
Set wks1 = Worksheets("Foglio1")
Set rng1 = wks1.Range("A:A")
' Destiny
Set wks2 = Worksheets("Foglio2")
' Searching
For Each rng In rng1
Select Case rng
Case "a"
' put the value in the first column
' in the destination sheet
wks2.Cells(rng.Row, 1) = Cells(rng.Row, 2)
Case Else
' nothing
End Select
Next
End Sub

Regards
Eliano