Macro to limit fill-down rows

  • Thread starter Thread starter Mike Fogleman
  • Start date Start date
M

Mike Fogleman

I have a formula that needs to be copied down column E alongside imported
data in column D. The number of rows that the imported column D has varies
with each import. How can I set my fill-down rows for the formula to match
up with column D?
 
This doesn't seem to work. Here is my code for cell E1, and I want it to
fill down to wherever column D data ends.

Range("E1").Select
ActiveCell.FormulaR1C1 = _

"=CONCATENATE(CONCATENATE(IF(LEN(RC[-4])=5,LEFT(RC[-4]),LEFT(RC[-4],2)),""/"
",LEFT(RIGHT(RC[-4],4),2),""/"",RIGHT(RC[-4],2)),""
"",CONCATENATE(IF(LEN(RC[-3])=3,LEFT(RC[-3]),LEFT(RC[-3],2)),"":"",RIGHT(RC[
-3],2)))"
Range("E1", Range("E1").End(xlDown)).Offset(0, 1).FillDown

Can you see what is wrong here?
 
Back
Top