Copy Paste Formula in alternate Rows without breaking link

[vvkprabhu]

I want to copy and paste the formula in alternate row without breaking the link.

I have attached an example with explanation. Please help!

This is required for a range of more than 1000 rows & i want to create a macro for this.

[jolivanes]

When I copy from Row 5 to Row 7, formula is linking to row 7 of input file
Where as I want the formula to be linked to row 6 of input file.

When you copy to Row 9, what do you want the reference to be? 7 or 8.
And to Row 11, 8, 9 or 10

[vvkprabhu]

Thanks for your reply. The copy & paste formula should be in series 6,7,8,9,10... and so no.

When i copy to Row 9, the reference to be 7
When copy to Row 11, the reference to be 8 and so on....

When you copy to Row 9, what do you want the reference to be? 7 or 8.
And to Row 11, 8, 9 or 10

[jolivanes]

Would this work?

Sub Expand_Formulae()
    Dim Message, Title, Default, MyValue, i As Long, j As Long
    Message = "Please enter the Row number you want to expand to"
    Title = "Use an odd number please"
    Default = ""
    MyValue = InputBox(Message, Title, Default)
    Application.ScreenUpdating = False
    Range("A5:K5").Value = "=Input!RC"
    j = 1
    For i = 7 To MyValue Step 2
        Range("A" & i & ":K" & i).Value = "=Input!R[-" & j & "]C"
        j = j + 1
    Next i
    Application.ScreenUpdating = True
End Sub

[vvkprabhu]

Hi Jolivanes,

Thanks for your reply.

I would be very help if you can update this logic in the excel file provided by me & attach here.

Thanks,
Vivek.

Would this work?

Sub Expand_Formulae()
    Dim Message, Title, Default, MyValue, i As Long, j As Long
    Message = "Please enter the Row number you want to expand to"
    Title = "Use an odd number please"
    Default = ""
    MyValue = InputBox(Message, Title, Default)
    Application.ScreenUpdating = False
    Range("A5:K5").Value = "=Input!RC"
    j = 1
    For i = 7 To MyValue Step 2
        Range("A" & i & ":K" & i).Value = "=Input!R[-" & j & "]C"
        j = j + 1
    Next i
    Application.ScreenUpdating = True
End Sub

[jolivanes]

See attached

[vvkprabhu]

Thank you Jolivanes. This will help me a lot. However, i have one more query.

Instead of manually updating the no. of rows everytime i click the expand button, is it possible to apply this logic through macro with a predefined range.

For example: I want to copy the data from row 5 and paste in alternate row till row 55. Here 55 is already predefined and in the single click the data should get copied in alternate row upto row 55.

[jolivanes]

Try this.

Sub Expand_Formulae_A()
Dim i As Long, j As Long
    Application.ScreenUpdating = False
    Range("A5:K5").Value = "=Input!RC"
    j = 1
    For i = 7 To 55 Step 2
        Range("A" & i & ":K" & i).Value = "=Input!R[-" & j & "]C"
        j = j + 1
    Next i
    Application.ScreenUpdating = True
End Sub

[vvkprabhu]

Hi Joivanes,

Thank you very much. This is working fine.

Try this.

Sub Expand_Formulae_A()
Dim i As Long, j As Long
    Application.ScreenUpdating = False
    Range("A5:K5").Value = "=Input!RC"
    j = 1
    For i = 7 To 55 Step 2
        Range("A" & i & ":K" & i).Value = "=Input!R[-" & j & "]C"
        j = j + 1
    Next i
    Application.ScreenUpdating = True
End Sub

[jolivanes]

Glad you've got it working.
Thanks for letting us know.
Good luck