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

vidyavallaba

New Member
Joined
Dec 17, 2013
Messages
42
Hi,

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

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??:confused:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
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...

Code:
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

New Member
Joined
Dec 17, 2013
Messages
42
Give this macro a try...

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,976
Office Version
  1. 2016
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top