Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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$10 00="A", ROW($A$1:$A$1000)),ROW()))) -- Jacob "lilianld" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 "Jacob Skaria" wrote: 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$10 00="A", ROW($A$1:$A$1000)),ROW()))) -- Jacob "lilianld" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
Try the same formula in Sheet2 cell A1 and copy down as required..
-- Jacob "lilianld" wrote: 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 "Jacob Skaria" wrote: 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$10 00="A", ROW($A$1:$A$1000)),ROW()))) -- Jacob "lilianld" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 "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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
No one can help? :(
"lilianld" wrote: 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 "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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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. "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 . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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. "Jef Gorbach" wrote: 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 . |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search the column and return a cell value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
Search, Match, And return corresponding column value | Excel Worksheet Functions | |||
Search a column for a value and return T or F | Excel Discussion (Misc queries) | |||
Search one column return value from another | Excel Programming | |||
URGENT -- search in string for a value in another column, if found, return value from next column | Excel Programming |