Search the column and return a cell value

[lilianld]

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

 

[Joe]

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

 

[Jacob Skaria]

If you would like to use a formula you could try the below in Sheet2 and copy
down as required. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=IF(COUNTIF(Sheet1!$A$1:$A$1000,"a")<ROW(A1),"",
INDEX(Sheet1!C$1:C$1000,SMALL(IF(Sheet1!$A$1:$A$1000="A",
ROW($A$1:$A$1000)),ROW())))

 

[lilianld]

Thanks for your quick reply,

The code works perfectly except the fact that I wanted to populate the cells
in sheet2 without spaces, one "a" after another (consecutively).

I'll have to think how to modify this macro.

Thanks

 

[lilianld]

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

 

[Jacob Skaria]

Try the same formula in Sheet2 cell A1 and copy down as required..

 

[lilianld]

Hi Joe,

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

Thanks

 

[eliano]

Hi Joe,

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

Thanks













- 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

 

[lilianld]

Hi Eliano,

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

 

[lilianld]

No one can help?

Hi Eliano,

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

 

[Jef Gorbach]

Give this a shot:

Sub test()
Cells.AutoFilter field:=1, Criteria1:="a"
Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Sheet2").Range("A4")
Application.CutCopyMode = False
Cells.AutoFilter 'turn off filter
Worksheets("Sheet2").Columns("A:B").Delete 'since we only want
column(c) values
End Sub

 

[eliano]

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 ata
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








- 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

 

[lilianld]

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 lot Eliano.

 

[lilianld]

Hi Jef,

Thanks for trying to help.
The macro works great. Does the job but I did not delete anything on sheet2
because it contains tables.

 

[eliano]

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.activateat
the end of the macro (not sure if this is the most effective method but it
works)

Thanks a lotEliano.








- 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

 

[eliano]

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