copy in vba

alisona

New Member
Joined
Mar 3, 2008
Messages
27
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
This ought to do it for you:

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

Hope that helps,
 

alisona

New Member
Joined
Mar 3, 2008
Messages
27
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

Code:
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)"
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Did not work.

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

What happens when you try entering it via code?
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
So what doesn't work? Does it not array enter? Does it not give you the expected results?
 

alisona

New Member
Joined
Mar 3, 2008
Messages
27
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,323
Members
414,442
Latest member
Oleg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top