Help: Need macro that copies formula based on condition

Hello,

I'm new to the forum. Was wondering if you guys could help me with the following:

I have a a table that has Lengths (km) with corresponding Temperature (°C). However, the last length value does not have a temperature associated with it. I would like to create a macro that goes to the last length value and checks to see if there is a temperature associated with it, if not it will go to the next temperature column (2 columns down) and copy the first temperature value. I would like to repeat this for all length/temperature columns.

In the picture the yellow cells are the empty temperature cells that need to have the value of the first temperature cell (white cell) copied over. However, the last temperature cell in the table (red cell) should be left empty.
Table.PNG

I made a really rudimentary macro using Record Macro, and it works fine. However, it only works for this workbook. I have other workbooks with the same kind of table but with more/less Lengths & Temperatures values so I can't use the recorded macro on those. This is the macro I am currently using - it is cell reference specific and I need to make it so it can work on any number of values for columns:

Sub CopyEndRef()
'
' CopyEndRef Macro
' copy reference from beginning of next section over to end of last section
'
Range("B26").Select
ActiveCell.FormulaR1C1 = "=R4C[2]"
Range("B26").Select
Selection.Copy
Range("B26,D14,F10,H6,J704,L17,N6,P12,R7,T8").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Any help would be much appreciated!

Thanks

Thanks for the suggestion, unfortunately it doesn't work as intended. It instead copies a different value over and there are still some cells that did not get copied over.

I have attached the workbook to play around with.

Thanks! That worked magic!!

Now I just have to try and understand what you did!!

And another similar way.

Please Login or Register  to view this content.

Thanks jolivanes, this also works perfectly.

You can replace this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

If you don't need/want a formula

Good luck and thanks for letting us know.