copy in vba

[alisona]

Hi there,

I want to copy a formula from a top cell to a last cell and the adjacent cell will always contain data. For example a1 is 1, b1 is 2, c1 is the formula (=a1+b1) = 3 and etc... I would like to copy c1 to c5 but sometimes I can have over 3,000 rows. Every time the number of rows are different. Thank you very much!


<table x:str="" style="border-collapse: collapse; width: 144pt;" border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" x:num="" align="right" height="17" width="64">1</td> <td style="width: 48pt;" x:num="" align="right" width="64">2</td> <td style="width: 48pt;" x:num="" x:fmla="=A1+B1" align="right" width="64">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2</td> <td x:num="" align="right">3</td> <td x:num="" x:fmla="=A2+B2" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3</td> <td x:num="" align="right">4</td> <td x:num="" x:fmla="=A3+B3" align="right">7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">4</td> <td x:num="" align="right">5</td> <td x:num="" x:fmla="=A4+B4" align="right">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">5</td> <td x:num="" align="right">6</td> <td x:num="" x:fmla="=A5+B5" align="right">11</td> </tr> </tbody></table>
Alison

 

[Smitty]

Try it this way:

Range([e2], Cells(Rows.Count, "d").End(xlUp).Offset(, 1)).FormulaArray = _
    "=INDEX(second!RC[-1]:R[4]C[-1],MATCH(RC[-4]&RC[-3]&RC[-2],second!R2C1:R6C1&second!R2C2:R6C2&second!R2C3:R6C3),0)"

I suspect that your problem is that you first enter the array, then try to change it, which array's don't like, so you need to apply it to the entire range first.

Debug on the last item (starting range("e")

It always helps to give the exact error message as well.

 

[alisona]

I got it to work. Thanks so much anyway.