VBA to paste a formula till a specific number of columns to the right

[yam26]

Hi,


I'm looking for some simple VBA to put in a macro that will enable me to copy a formula from a cell and paste the formula into the cells to the right till a set number of columns, the number of columns being specified in another cell...


Eg. formula is in cell A2, cell A1 contains the number 50, and when executed the VBA populates cells B2:AZ2 with the formula in A1


Hope you may be able to assist.


Regards,
Chris

 

[AlphaFrog]

[color=darkblue]Sub[/color] MyFormula()
    Range("A2").Copy Destination:=Range("B2").Resize(1, Range("A1").Value)
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Peter_SSs]

Welcome to the MrExcel board!

Not quite sure how you got that as B:AZ is 51 columns not 50 but you should be able to adapt this.

Sub Fill_Right()
    Range("A2").Resize(, Range("A1").Value + 1).FillRight
End Sub

 

[yam26]

Thank you for your responses.

I tried them but it doesn't work for a range of formulae.
For example, Cell A2 and B2 have different formulae. If I drag it down manually, Cell C2 will contain the formula in A2 and D2 will contain the formula in B2, and so on.
Is there any way to do this through VBA code?

 

[Peter_SSs]

Thank you for your responses.

I tried them but it doesn't work for a range of formulae.

Your question did not mention a range of formulae, just a formula in A2.

For example, Cell A2 and B2 have different formulae. If I drag it down manually, Cell C2 will contain ....

- "drag it down" Drag what down?
- If you drag A2 or B2 or A2:B2 down, nothing will go into C2. Dragging down anything from row 2 will populate rows 3, 4, 5, etc, not anything in column C

Perhaps you could explain again carefully exactly what you have and what you are trying to achieve?

 

[yam26]

Hi Peter,

My explanation was vague earlier.

Please refer to the following table:

	A	B	C	D	E
1	5	(formula1)	(formula2)
2

<tbody>
</tbody>

The formula in B1 should also be in cells D1, F1, H1, and so on
The formula in C1 should also be in cells E1, G1, I1, and so on

The number of times the above process needs to repeat is in cell A1

Normally I could just select the range (B1:C1) and drag it to the right and the alternate cells would have respective formulae. I need a code to do just that.

Please help me with this

 

[Peter_SSs]

I'm still not entirely clear on the number of copies but it should be one of these.
With 5 in A1 ...

- this will copy B1:C1 to 5 more sets of 2 columns making 6 sets of 2 columns (12 columns)

Range("B1:C1").Copy Destination:=Range("D1").Resize(, Range("A1").Value * 2)

- this will copy B1:C1 and paste so that in total there are 5 sets of 2 columns (10 columns)

Range("B1:C1").Copy Destination:=Range("B1").Resize(, Range("A1").Value * 2)

.. or another way to do those same 2 things

Range("B1:C1").AutoFill Destination:=Range("B1").Resize(, (Range("A1").Value + 1) * 2), Type:=xlFillDefault
Range("B1:C1").AutoFill Destination:=Range("B1").Resize(, Range("A1").Value * 2), Type:=xlFillDefault

 

[yam26]

Thanks Peter, that worked perfectly!

 

[Peter_SSs]

Thanks Peter, that worked perfectly!

You're welcome. Important to clearly describe/show what you have and what you want as we are not there to look at it.