PDA

View Full Version : [SOLVED] VBA Copy formula x times to the right based on value in cell



Therese
10-02-2017, 01:46 PM
Hi everybody,

I would like to ask if somebody could help me with my problem. I´m still learning VBA and I have difficulties to write codes.
What I´m trying to get.
I have value in cell A1 ( for example 5) In cell B2 I have formula A2-1. And I want to copy formula from B2 to the right 5 times. So, in cell C2 is going to be B2-1, in D2 => C2-1 etc.

I´ve found code which does exactly what I want but instead of copying to right it copied down. I´ve tried to changed it but it does not work.

Please could somebody tell me where is mistake and how to correct it? Or if there is a different way how to do it.


Sub copytoright()


Dim y As Range
Dim yy As Long

yy = Cells(2, Columns.Count).End(xlToLeft).Column
Set y = Range("A1")
Do Until y = 0
Range("B2").Copy Destination:=Range("C" & yy + 3)
yy = Cells(2, Columns.Count).End(xlToLeft).Column
y = y - 1
Loop
End Sub


Thanks a lot,

Therese

mdmackillop
10-02-2017, 02:00 PM
A couple of ways to consider

Sub Test1()
Dim yy As Range
Set yy = Cells(2, Columns.Count).End(xlToLeft)
yy.Resize(, Range("A1") + 1).FillRight
End Sub


Sub Test2()
Dim yy As Range
Dim i As Long
Set yy = Cells(2, Columns.Count).End(xlToLeft)
For i = 1 To Range("A1")
yy.Copy yy.Offset(, i)
Next
End Sub

Sub Test3()
Dim yy As Range
Dim i As Long
Set yy = Cells(2, Columns.Count).End(xlToLeft)
For i = 1 To Range("A1")
yy.Copy Cells(2, 2 + i)
Next
End Sub

p45cal
10-03-2017, 05:27 AM
or:
Sub blah()
Set yy = Cells(2, Columns.Count).End(xlToLeft)
yy.Resize(, Range("A1").Value + 1).FormulaR1C1 = yy.FormulaR1C1
End Sub

Therese
10-03-2017, 10:36 AM
Thanks a lot to both of you. It works.
You helped me so much.