On 20 Feb, 23:57, eliano wrote:
On 20 Feb, 04:23, lilianld wrote:





That exactly what I was trying to achieve.
I just noticed that it takes the value from column C just from the active
sheet only. But I just added wk1.activate before R = 5 and wk2.activate at
the end of the macro (not sure if this is the most effective method but it
works)

Thanks a lotEliano.

"eliano" wrote:
On 19 Feb, 04:34, lilianld wrote:
HiEliano,

Much appreciated for trying to help.
I still can't get the right result with the modified macro.

Maybe I did not explained very well what I was trying to achieve. And I
think I'll have to use some looping functions.

What the below macro does it finds an "a" in sheet1, column A and put an "a"
in the sheet2 on the same row.
I was expecting to find and "a" in sheet1, column A (let say it found it at
A7) then take the value from column "C" in the same row (in this case it
would be "C7") and populate this value in sheet2 in column A, starting at a
certain position (ex. A5). After that to continue for the next "a" in column
A of sheet1 and return the value of column "C' for he corresponding position
of column "A" (ex. the second "a" was found on "A17" then take the value from
"C1" and put it in sheet2 in "A6" after the first "a" was found.

Thanks again

"eliano" wrote:
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
.- Nascondi testo citato

- Mostra testo citato -

Hi liliand.
If I have understood, try:

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

Regards
Eliano
.- Nascondi testo citato

- Mostra testo citato -

Hi Lili.
I believe that activation is not necessary, as the value is taken from
Foglio1, that is: Sheet1.
Thanks for the feedback,Eliano- Nascondi testo citato

- Mostra testo citato -

Sorry Lili, but i don't see two lines in my previous msg. That is:

Change: wks2.Cells(R, 1) = Cells(rng.Row, 3)
in: wks2.Cells(R, 1) = wks1.Cells(rng.Row, 3)

Regards
Eliano