Copying formula to a range of cells and convert them to value

[vidyavallaba]

Hi,

I tried recording and macro but it did not help me.

I have a formula in "B2" and i want to copy it till last row of B.

last_row=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

and then i want to copy the values in B2 to last_row and paste them in column c.

Can anyone help me??

 

[Rick Rothstein]

I have a formula in "B2" and i want to copy it till last row of B.

last_row=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

and then i want to copy the values in B2 to last_row and paste them in column c.

Give this macro a try...

Sub FillDownColBandCopyToColA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow).Formula = Range("B2").Formula
  Range("C2:C" & LastRow).Value = Range("B2:B" & LastRow).Value
End Sub

 

[vidyavallaba]

Thanks for the quick reply..

But the code is not working

 

[vidyavallaba]

Give this macro a try...

Sub FillDownColBandCopyToColA()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow).Formula = Range("B2").Formula
  Range("C2:C" & LastRow).Value = Range("B2:B" & LastRow).Value
End Sub

Thanks for the help Rick. I have gone through your blogs and posts. They are of great help for beginners like me

Sub FillDownColBandCopyToColA()
Dim LastRow As Long
Dim i As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B1").Copy
Range("B1:B" & LastRow).PasteSpecial xlPasteFormulas
Range("B1:B" & LastRow).Copy
Range("B1:B" & LastRow).PasteSpecial xlPasteValues

End Sub

I have the above code working

 

[Rick Rothstein]

Thanks for the quick reply..

But the code is not working

I do not know why it did not work for you... I tested the code before I posted it and it worked fine here on my compute using X2003.

Thanks for the help Rick. I have gone through your blogs and posts. They are of great help for beginners like me

I am glad you found them useful... thanks for letting me know

Thanks for the help Rick. I have gone through your blogs and posts. They are of great help for beginners like me

Sub FillDownColBandCopyToColA()
Dim LastRow As Long
Dim i As Integer
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("B1").Copy
Range("B1:B" & LastRow).PasteSpecial xlPasteFormulas
Range("B1:B" & LastRow).Copy
Range("B1:B" & LastRow).PasteSpecial xlPasteValues

End Sub

I have the above code working

I am glad you were able to get something to work for you (although I still do not understand why what I posted did not work for you).