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]

i installed excel jennie, i'm able to get an image of the workbook, but unable to find out, how to post that image here.i'm using excel 2007 and IE9.
my above example didn't give u idea, it was quiet simple, or may i post the workbook of some sharing place or email u, coz these things are just wasting time.and i dun have one.

it'll be considerate of u if u help me out of this situation

 

[Peter_SSs]

i installed excel jennie, i'm able to get an image of the workbook, but unable to find out, how to post that image here.i'm using excel 2007 and IE9.
my above example didn't give u idea, it was quiet simple, or may i post the workbook of some sharing place or email u, coz these things are just wasting time.and i dun have one.

it'll be considerate of u if u help me out of this situation

I, and many helpers here, choose not to download files from other sites. Sounds like you are almost there with Excel jeanie anyway.

1. Select the area you want to show (not too big please).

2. Click the left hand button on the jeanie toolbar in the Add-Ins ribbon tab.

3. Click the Forum Standard button near the bottom left of the jeanie screen. (A screen image may appear at this point. Just close it.)

4. Move directly to your post, put your cursor where you want the image and Paste. (It will possibly look like a lot of rubbish but don't worry, move to step 5)

5. After writing any further explanation you want, click Submit Reply

 

[fari1]

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>Y</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >SERVICES-COMPUTER PROGRAMMING, DATA PROCESSING, ETC.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >State location: CA | State of Inc.: DE | Fiscal Year End: 1231</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >(Assistant Director Office No 3)</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Get insider transactions for this issuer.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Get insider transactions for this reporting owner.</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Filter Results:</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Items 1 - 35  0001288776 Filings RSS Feed</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Filings</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >10-K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >37</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >38</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >39</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >40</td><td >10-Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >41</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >42</td><td >10-K</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[fari1]

that rubbish i had already done way before, but thought, i wasn't of any use anyways my 2nd image tells u the story, here's my actual post, i need to have 10-K,10-Q,10-Q/A,10-k/A out of this column and paste it in sheet3, range A10 after clearing the earlier used range from A10

 

[Peter_SSs]

Glad to see Excel jeanie working!

Try this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> fari2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet3")<br>        lr = .Range("A" & .Rows.Count).End(xlUp).Row<br>        <SPAN style="color:#00007F">If</SPAN> lr >= 10 <SPAN style="color:#00007F">Then</SPAN><br>            .Range("A10:A" & lr).ClearContents<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        fr = .Columns("A").Find(What:="Filings", LookIn:=xlValues, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Row<br>        lr = .Range("A" & .Rows.Count).End(xlUp).Row<br>        rws = lr - fr + 1<br>        .Range("A" & fr).Resize(rws).AdvancedFilter Action:=xlFilterCopy, _<br>            CopyToRange:=Sheets("Sheet3").Range("A10"), Unique:=<SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet3").Range("A10").Resize(rws)<br>        .Cells(1, 1).ClearContents<br>        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[fari1]

u've given me another code for it, let me try this, if it works, will be glad, if u'll combine both of these codes together. ur earlier code and this one, let me just tell u after running it

 

[fari1]

        .Range("A" & fr).Resize(rws).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Sheets("Sheet3").Range("A10"), Unique:=True

its saying subscirpt out of range and highlighting above part

 

[fari1]

sorry my mistake, didn't change the sheet name, its copying but giving the wrong results, some mixed data

 

[Peter_SSs]

u've given me another code for it, let me try this, if it works, will be glad, if u'll combine both of these codes together. ur earlier code and this one, let me just tell u after running it

I can't combine without more info. Previously we looked for 'A' and 'B' but not 'C'. Now we don't have any of those values so what are we looking for in column A? And what does column C look like now?

 

[fari1]

hey, in your code, where u've defined that u want to copy just 10-k,10-Q,10-K/A,10-Q/A???