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

[lisajones]

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.

 

[JLGWhiz]

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]

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]

If you just copy the cell like

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

Range("A2").Formula = Range("A1").Formula

Then it keeps the original reference.

 

[JLGWhiz]

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

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

ActiveCell.Offset(1).Activate

 

[lisajones]

Then ;you want code something like

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

ActiveCell.Offset(1).Activate

Thank you. I have tried this:

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?

 

[Fluff]

How about

Cells(Rows.Count, "E").End(xlUp).Resize(2).FillDown

 

[lisajones]

Thanks for the replies. With a bit of tweaking from all of your responses I managed to come up with this:

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

And it worked!!!!!

Thanks again

 

[JLGWhiz]

Glad we could help,
Regards, JLG