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

 

[bensonsearch]

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

are you looking for a formula or vba coding? if coding i would use a vlookup for particular text (granted we know what it is) and then select all fields with data and paste into sheet 2.

 

[fari1]

hi thanks for the reply, i'm looking for code, as vlookup cant be used, because there are similar texts against which data needs to be found

 

[bensonsearch]

hi thanks for the reply, i'm looking for code, as vlookup cant be used, because there are similar texts against which data needs to be found

im confused, if you just have 2 coloums and want data from one side a vlookup can do that (in vba code of course).

anyhow do you have a sample workbook?

 

[Peter_SSs]

fari1

I'm also confused about just what your original data looks like.

Is it like below? If not can you post a small screen shot? My signature block has several methods to do that.

Is there, or could there be more data below this? For example, D: Banana

If you could also show or describe accurately just what the results should be and how they should be laid out in Sheet2 it would help.

Excel Workbook
	A	B	C
1	Code		Description
2	A		Fruit
3			Apple
4	B		Fruit
5			Mango
6	C		Fruit
7			Orange
8
Sheet1

 

[fari1]

yes, it is exactly like that, and what i want is to get the values against just A and B..........and not the C, the cell below fruit,e.g mango,grapes etc

hope i made it clear now, the rest data is nothing, i just want whenevr there is A or B in column 1 then it brings me data from the Column C after the cell fruit.

 

[Peter_SSs]

hope i made it clear now

Not really. I have no idea where grapes comes from and the following two statements seem to contradict each other:

i want to get the values against B and C i-e just mango and orange

get the values against just A and B..........and not the C, the cell below fruit,e.g mango,grapes etc

Anyway, test this in a copy of your workbook and see if it is somewhere near the mark.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> fari1()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<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><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">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">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>        .UsedRange.ClearContents<br>        .Range("A1:A" & UBa).Value = b<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]

u'r the bestttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt man

 

[fari1]

With Sheet2
        .UsedRange.ClearContents
        .Range("A1:A" & UBa).Value = b
End With

hi peter, apart from this line, i want the code to bring in data in sheet A20 and clear the earlier range starting from A20 to down. i tired to change it myself, but its not working somehow

 

[Peter_SSs]

With Sheet2
        .UsedRange.ClearContents
        .Range("A1:A" & UBa).Value = b
End With

hi peter, apart from this line, i want the code to bring in data in sheet A20 and clear the earlier range starting from A20 to down. i tired to change it myself, but its not working somehow

Replace that section with this

With Sheets("Sheet2")
    c = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A20:A" & IIf(c < 20, 20, c)).ClearContents
    .Range("A20").Resize(UBa).Value = b
End With