PDA

View Full Version : [SOLVED] Copy Paste Formula in alternate Rows without breaking link



vvkprabhu
11-26-2014, 07:09 AM
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
11-26-2014, 11:40 PM
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
11-27-2014, 01:52 AM
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
11-27-2014, 11:02 AM
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
11-28-2014, 03:37 AM
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
11-28-2014, 09:39 AM
See attached

vvkprabhu
11-28-2014, 09:43 PM
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
11-28-2014, 10:11 PM
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
11-28-2014, 11:38 PM
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
11-29-2014, 12:14 PM
Glad you've got it working.
Thanks for letting us know.
Good luck