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