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]

This ought to do it for you:

Range("C1").Copy Range([C1], Cells(Rows.Count, "B").End(xlUp).Offset(, 1))

Hope that helps,

 

[alisona]

you are the best

 

[alisona]

I want to include this formula in a macro
{=INDEX(Sheet2!D2:D6,MATCH(A2&B2&C2,Sheet2!$A$2:$A$6&Sheet2!B2:B6&Sheet2!$C$2:$C$6,1),0)}

What is the VBA code?

 

[Smitty]

The easiest thing to do is record entering the formula as an array:

Selection.FormulaArray = _
        "=INDEX(Sheet2!R[1]C[3]:R[5]C[3],MATCH(R[1]C&R[1]C[1]&R[1]C[2],Sheet2!R2C1:R6C1&Sheet2!R[1]C[1]:R[5]C[1]&Sheet2!R2C3:R6C3,1),0)"

 

[alisona]

Did not work.

 

[Smitty]

Did not work.

That doesn't really help. Does the formula work on its own?

What happens when you try entering it via code?

 

[alisona]

Yes the formula itself work fine.

 

[Smitty]

So what doesn't work? Does it not array enter? Does it not give you the expected results?

 

[alisona]

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


Range("e2").Select
ActiveCell.FormulaR1C1 = _
"=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)"

Selection.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)"
Range("e2").Copy Range([e2], Cells(Rows.Count, "d").End(xlUp).Offset(, 1))