copy data in row below.

[donlincolnmre2]

Hello

I'm looking for a macro that will move the numbers in column F

If a 10 digit number is found in COLUMN E, then the macro should go to PREVIOUS ROW and then to COULUMN F OF THAT ROW and copy that number 1 ROW BELOW.

Here is the excell data

https://docs.google.com/spreadsheet/ccc?key=0Am2ZfIUjd5StdFhyQU9tTzJOdnR0LWxNS05CLWlQTUE&hl=en_US

any help would be greatly appreciated

Thanks.

 

[Peter_SSs]

To me, your description is not clear.

If a 10 digit number is found in COLUMN E

For example, that is true in row 3.

go to PREVIOUS ROW and then to COULUMN F OF THAT ROW and copy that number

The previous row to row 3 is row 2, but there is no number in column F row 2.

What would the results look like for that sample starting data?

 

[donlincolnmre2]

Here is the Result should looks like, hopefully this will clarify it.

thanks.

https://docs.google.com/spreadsheet/ccc?key=0Am2ZfIUjd5StdE5UN2JDOGVSZDZhbkd3SFFhbTZ3Znc&hl=en_US

 

[Peter_SSs]

Yes, that helps with understanding you original description but can you confirm that F18:F19 should be 27900 not 992200?

 

[donlincolnmre2]

yeah that should be

992200

just over looked,

 

[Peter_SSs]

Try this in a copy of your workbook.

Rather than looking for 10 digit numbers in column E, it just fills the blanks in column F. For the samples given, that seems to amount to the same thing. Is that okay?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FillGaps()<br>    <SPAN style="color:#00007F">Dim</SPAN> Gaps <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Range("C" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">With</SPAN> Range("F2:F" & lr)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Gaps = .SpecialCells(xlCellTypeBlanks)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Gaps <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>            Gaps.FormulaR1C1 = "=R[-1]C"<br>            .Value = .Value<br>            Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><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">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[donlincolnmre2]

Yeah that worked great

Thanks a Lot.