Find a Text VBA Code

[fari1]

i want a code, that finds a text in column C i-e Description and copy the data below it,
the data in the rows after this word has to be copied with the below mrthod.

Description

AFruit
Apple
B Fruit
Mango
C Fruit
Orange

these A, B and C are in column 1, i want to get the values against B and C i-e just mango and orange from column C and get it pasted in sheet 2, the range can be long or short or even with no values after description

 

[fari1]

yes it worked perfectly, i also posted in my another thread before u replied today, that i wanted to get those A,B values as well, but in my sheet3, what if u could add that function in this code, it would be easy to run just one code, right now, this code is bringing in values from Column C, based upon "A" and "B" in column A, while i also want all the "A" and "B", in column A of sheet3, if you could help me on this, this would complete my important part of project today. thanking u in adnvace

 

[Peter_SSs]

I'm not sure I understand this. You want the same thing we just put in Sheet2 also put in Sheet3?

If not, use the sample data in post #5 to say exactly what should be in column A of Sheet3, and what cell the results should start at.

In terms of your 'other thread', I have no idea which thread you are referring to so a link to it here would be useful if you think people need to see it to know what you are asking here.

 

[fari1]

http://www.mrexcel.com/forum/showthread.php?p=2811506#post2811506

here's the link to my other thread, let me further clarify this, in post No.5 of our thread,finding text vba code, you gave an example of data, right now, the code that u provided is looking for A and B in column A and giving its corresponding values from column C. I just want to get those values as well from Column A, against which we are getting values from Column C. All those "A" and "B" from Column A.

 

[fari1]

the code, he's giving on that thread is not working, wil u pl help

 

[Peter_SSs]

I can't really follow that other thread either. Part of the reason is that some of the thread has been conducted privately by email. Please kjeep in mind #7 of the Forum Rules and 18 and 19 of the Posting Guidelines.

In relation to the sample data from post #5, would you want
A
(space)
B
(space)


or just
A
B

You didn't answer my question about exactly where in Sheet3 you want these results.

As a general note you would get much better and faster help if you would post small sample screen shots directly in your posts (like I did in post #5). It is then clear exactly what the data is like and what rows/columns it is in etc.

 

[fari1]

i'm sorry, if i voilated any of the forum rules, i didn't posted the screenshot, its because, u already understood and posted exactly what i need, i need just

A
B

not the spaces.
and i want these results in sheet3 starting from Column A10 and clear whatever was earlier before posting these results

 

[fari1]

its in column A

 

[Peter_SSs]

i didn't posted the screenshot, its because, u already understood and posted exactly what i need,

But of course if you had posted such a screen shot first I wouldn't have had to make that post to ask if I had the right idea.
Also, other helpers may have already looked before me and just pased on because they weren't quite sure of your data and layout.

i need just

A
B

This seems a strange request since we are searching for values next to A and B and then reporting A and B in Sheet3. Could we just go straight to Sheet3 and put A and B in A10 and A11?

In case for some reason the answer is 'No', try this modification (though it seems a lot of extra work to extract something we already know we are looking for). It does assume Excel 2007 or later.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> fari1()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b, y<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, UBa <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, d <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bGo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        a = .Range("A2", .Range("C" & .Rows.Count).End(xlUp))<br>        UBa = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> UBa, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">ReDim</SPAN> y(1 <SPAN style="color:#00007F">To</SPAN> UBa, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> UBa<br>        <SPAN style="color:#00007F">If</SPAN> a(i, 1) = "" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> bGo <SPAN style="color:#00007F">Then</SPAN><br>               c = c + 1<br>               b(c, 1) = a(i, 3)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            bGo = (a(i, 1) = "A" <SPAN style="color:#00007F">Or</SPAN> a(i, 1) = "B")<br>            <SPAN style="color:#00007F">If</SPAN> bGo <SPAN style="color:#00007F">Then</SPAN><br>                d = d + 1<br>                y(d, 1) = a(i, 1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        c = .Range("A" & .Rows.Count).End(xlUp).Row<br>        .Range("A20:A" & IIf(c < 20, 20, c)).ClearContents<br>        .Range("A20").Resize(UBa).Value = b<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet3")<br>        c = .Range("A" & .Rows.Count).End(xlUp).Row<br>        .Range("A10:A" & IIf(c < 10, 10, c)).ClearContents<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A10").Resize(UBa)<br>            .Value = y<br>            .RemoveDuplicates Columns:=1, Header:=xlNo<br>            .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[fari1]

I TRIED TO MAKE THOSE ADDINS WORK, BUT I'M UNABLE TO SORT OUT HOW TO POST SCREENSHOT HERE I'M SORRY, BELOW IS WHAT MY ACTUAL DATA LOOKS LIKE, EACH ENTRY BELOW IS IN A SEPARATE COLUMN AND CONTAINS BLANKS AS WELL. YOUR CODE IS NOT WORKING ON BELOW DATA

Current Page
Mailing Address 1600 AMPHITHEATRE 
Business Address 
ABC Inc.SERVICES-COMPUTER PROGRAMMING, 
DATA PROCESSING, ETC.
State location: CA | State of Inc.: DE | 
(Assistant Director Office No 3)
Filter Results:
 
Filings
 
A
 
B
 
A
 
C
 
D
 
A
 
B
 
C
 
A
 
B
 
B

 

[Peter_SSs]

I TRIED TO MAKE THOSE ADDINS WORK, BUT I'M UNABLE TO SORT OUT HOW TO POST SCREENSHOT HERE I'M SORRY,

Are you able to describe at what point you ran aground with the Add-Ins? We may be able to help you past the problem.

The third method in my signature block is not an Add-In, did you try that method?

What version of Excel are you using?

What web browser are you using?