VBA code to copy exact formula with some change

[liiamra]

Hello Everyone!
This is the first time for me to ask for assistance regarding excel! and so I don't know which forum is the most helpful- I have tried one forum yesterday but no one gave me not even a hint- so I hope I get the required assistance here.

I have an excel sheet with relatively large data set. I have a formula with locked and unlocked reference cells: I want to be able to copy the formula down - so that the unlocked references change; Now the problem is that I want to copy the exact formula to the right of the second cell from top, then change the locked reference cells to the next one below. For example: If the formula in C1 is $A$5+B1, then I copy it down so I have in C2 $A$5+B2, C3: $A$5+B3.... now I want to copy the exact formula in C2 to D2 but changing the locked reference to the one below so I have in D2: $A$6+B2; afterwards I copy the formula in the second populated cell in D coloumn to E so I have in E3:$A$7+B3 and so on. Below is a table for illustration:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$5+B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$5+B2[/TD]
[TD]$A$6+B2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$5+B3[/TD]
[TD]$A$6+B3[/TD]
[TD]$A$7+B3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$5+B4[/TD]
[TD]$A$6+B4[/TD]
[TD]$A$7+B4[/TD]
[TD]$A$8+B4[/TD]
[/TR]
</tbody>[/TABLE]




Any help is highly appreciated.

Thanks and all best//

 

[circledchicken]

Hi liiamra and welcome to the forum,

Perhaps try this in C1 and drag/copy where required:
=INDEX($A:$A, 4 + COLUMNS($B1:B1)) + $B1

Alternatively, using an IF statement you can just drag the formula in C1 across to F1 and down to F4, something like this:

=IF(
   COLUMNS($B1:B1) <= ROWS(B$1:B1),
   INDEX($A:$A, 4 + COLUMNS($B1:B1)) + $B1,
   "")

Note:

• Please see the forum rules, especially on cross-posting to other forums (and you should probably post back there to let them know if you have found a satisfactory solution) - http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
• For more on the INDEX function, here is a nice article: The Imposing INDEX - Excel Hero Blog

 

[liiamra]

Hey CircledChicken!

Many many thanks- the index thing works like a charm! but is't possible to automate the process to definite number - for example a VBA code that runs 50 times and that is filling 50 columns?

To be more specific, is there a VBA code that fills the formula to the right cell below, for example it fills the formula from c2 to c50 first, then copy the formula to d3, fill to d51 and then copy to e4 - fill to e52 and so on?

Thanks a million mate!

P.S I am going to update the post there! telling that I have got the required assistance in here; I guess they wouldn't like it :D but going to do it anyways.

 

[circledchicken]

You're welcome - thanks for the feedback!

The VBA version is possible but I'm not sure why you would want it. Here's an example:

Sub example()

Dim oRng As Range
Dim i    As Long

Set oRng = Range("C1:C50")
oRng.Formula = "=INDEX($A:$A, 4 + COLUMNS($B1:B1)) + $B1"
oRng.Copy

For i = 1 To 49
    oRng.Offset(i, i).PasteSpecial
Next i

Application.CutCopyMode = False

End Sub

Note that this starts in C1 as per your original post rather than C2 but you can adjust the ranges/formula as appropriate.

 

[liiamra]

You rock mate!

I modified it according to my needs and it worked perfectly - perfect answer and assistance!

I needed to automate the process because I had to copy the formula to thousands of cells - now it is solved

Thanks a million mate- I do really appreciate your help now and always.

You have a good day!

 

[circledchicken]

I'm happy it helped - thanks and have a great day as well!