VBA code to copy the formula in the cell above whilst still keeping relative references.

lisajones

New Member
Joined
Oct 18, 2019
Messages
5
Hi all! I have managed to create a command button which will jump to the next empty cell in a column. However I then need it to also copy the formula from the cell above whilst keeping the relative cell references. For example, of the empty cell is E5, and the formula in D5 is =LookUpLists!K28, how do I copy the formula to E5 so that it is =LookUpLists!K29?

I have tried a few codes so far but they all seem to copy the formula exactly rather than as a relative reference.
Apologies I can’t copy and paste any of my code due to writing this on my phone and not my laptop.

Thank you.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The example is not in consonance with the objective you stated. E5 and D5 is from left to right, not above and below. However, Excel will automatically change the row reference if you copy from the cell above to the one below. And it will automatically change the column reference if you copy horizontally and it will change both column and row if you copy and paste diagonally. All so long as you do not have any cell refereces in the formula entered as Absolute reference with a preceding ($) symbol.
 

lisajones

New Member
Joined
Oct 18, 2019
Messages
5
Apologies, I should have written that I would like to copy the formula from E4 to E5. I am using a command button to do this so it needs to be in VBA code. I will then keep using the command button each time to copy the formula from E5 to E6, E6 to E7 and so on.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If you just copy the cell like
VBA Code:
Range("A1').Copy Range("A2")
Then it copies the formula in that cell and does the change to the relative reference.
But if you do
VBA Code:
Range("A2").Formula = Range("A1").Formula
Then it keeps the original reference.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Apologies, I should have written that I would like to copy the formula from E4 to E5. I am using a command button to do this so it needs to be in VBA code. I will then keep using the command button each time to copy the formula from E5 to E6, E6 to E7 and so on.
Then ;you want code something like
VBA Code:
ActiveCell.Copy ActiveCell.Offset(1)

It depends on how you are using the button to get the code so it goes to the next cell each time. The activecell would not change automatically when the button is clicked, so if you want the destination cell to then become the active cell, you would need to add that to the code
VBA Code:
ActiveCell.Offset(1).Activate
 

lisajones

New Member
Joined
Oct 18, 2019
Messages
5
Then ;you want code something like
VBA Code:
ActiveCell.Copy ActiveCell.Offset(1)

It depends on how you are using the button to get the code so it goes to the next cell each time. The activecell would not change automatically when the button is clicked, so if you want the destination cell to then become the active cell, you would need to add that to the code
VBA Code:
ActiveCell.Offset(1).Activate
Thank you. I have tried this:

VBA Code:
Private Sub cmbBatchNo_Click()

Cells(Rows.Count, "E").End(xlUp).Select
ActiveCell.Copy ActiveCell.Offset(-1)
ActiveCell.Paste

End Sub

but it's not pasting anything?
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Cells(Rows.Count, "E").End(xlUp).Resize(2).FillDown
 

lisajones

New Member
Joined
Oct 18, 2019
Messages
5
Thanks for the replies. With a bit of tweaking from all of your responses I managed to come up with this:

VBA Code:
Cells(Rows.Count, "E").End(xlUp).Select
ActiveCell.Offset(-1).Copy ActiveCell

And it worked!!!!!

Thanks again :)
 
Solution

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Glad we could help,
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,132,935
Messages
5,656,010
Members
418,264
Latest member
Reiper79

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