Paste value into first blank row in column range?

[kylesom]

Hi - I have a simple timesheet set up that includes a search function that returns a job number to cell A45.

I'd like to add a VBA macro button to automatically copy that job number in A45 and pastespecial (value only) into the first blank cell (counting from the top) in column A in the range A8:A38.

Am sure its pretty simple to do, but I've no idea where to start...any help appreciated!

 

[njimack]

Something like this (untested)

Sub a()
dim Last_Row as Long
Last_Row = Workshetfunction.Max(8,Range("A39").End(xlUp).Offset(1).Row
Range("A" & Last_Row).Formula = Range("A45").Value
End Sub

 

[kylesom]

Something like this (untested)

Last_Row = Workshetfunction.Max(8,Range("A39").End(xlUp).Offset(1).Row

Hi - thanks for the try but the line quoted is flagged in the the VBA editor as a Compile Error: Expected list seperator or ).

 

[njimack]

Oops - that's what I get for typing code straight onto the message board, without using Excel. It should be
Last_Row = WorksheetFunction.Max(8, Range("A39").End(xlUp).Offset(1).Row)

 

[kylesom]

Perfect - thanks!