Copy down formula then remove formula from where it copied with paste it as value

[ashupuneet]

Hi
I need help for VBA code. first it copy down formula's from 1st row (From column K to AA) to next row and then remove formulas from 1st row and paste it as a value in 1st row from where it copied. This will continue till it find a blank cell in J column.

Next time again it start from row (column K to AA) where previously stopped.
Formulas are in from column K to AA.


J
K
L
M
N
O
P
Q
R
S
till AA


NO
Crop
Time
day_1st
1st_time
day_2nd
2nd_time
day_3rd
3rd_time
Balance Capacity



4
CUL
0.2
4
0.2
8
0.07
0
0.07
27.47
From this row formula need to copy down to below row and then remove formula from this row followed by paste value.


4









this row need to have formulas copied down from above row


4












4













Thanks

[mana]

Sub test()
With Range("K" & Rows.Count).End(xlUp).Resize(, 17)
.Copy .Offset(1)
.Value = .Value
End With

End Sub

[mana]

Sorry, please try this.


Option Explicit

Sub test()
Dim c As Range

For Each c In Range("J2", Range("J" & Rows.Count).End(xlUp).Offset(-1))
With c.Offset(, 1).Resize(, 17)
.Copy .Offset(1)
.Value = .Value
End With
Next

End Sub

[ashupuneet]

Hi mana
Thanks for help but
When I run this macro second time on this same sheet, it is again start from row 2 in columns K:AA. instead of last cell in column K where formula is copied down.

Thanks

[mana]

Option Explicit

Sub test2()
Dim s As Long, e As Long
Dim i As Long

s = Cells(Rows.Count, "K").End(xlUp).Row
e = Cells(Rows.Count, "J").End(xlUp).Row - 1

For i = s To e
With Cells(i, "K").Resize(, 17)
.Copy .Offset(1)
.Value = .Value
End With
Next

End Sub

[ashupuneet]

Thanks A lot mana. Its working... Thank you

[ashupuneet]

HI,
this code is working for me but i need some addition to this macro like i want to copy and paste value of cell from column Y to Z. To copy and paste i am using one line highlighted in red but this range i am changing again- again when more rows added to the sheet and it is also running very slow
Sub test2()

Dim s As Long, e As Long
Dim i As Long

s = Cells(Rows.Count, "K").End(xlUp).Row
e = Cells(Rows.Count, "J").End(xlUp).Row - 1
For i = s To e
With Cells(i, "K").Resize(, 15)
.Copy .Offset(1)
Range("Z1:Z500").Value = Range("Y1:Y500").Value
.Value = .Value
End With
Next


End Sub