On 01/08/2015 10:09, Claus Busch wrote:
Hi,

Am Fri, 31 Jul 2015 12:40:39 +0100 schrieb F:

I've struggled, and failed, to write a macro to automate this. Can
anyone help?

why don't you write the values into C:F? Then you don't have to copy &
paste values. Paste the new data to K:L and then run the macro.

Sub Test()
Dim dest As Range

With ActiveSheet
Set dest = .Cells(Rows.Count, 3).End(xlUp)(2)
dest = Evaluate("=IF(INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$ 151),$L$8:$L$151,0))" _
& "<""00:10"",INDEX($K$8:$K$151,MATCH(MAX($L$8:$L$1 51),$L$8:$L$151,0)),""No output"")")
dest.Offset(, 1) = Evaluate("=MAX($L$8:$L$151)")
dest.Offset(, 2) =
Evaluate("=INDEX(K8:K80,MATCH(TRUE,L8:L80<0,0))")
dest.Offset(, 3) =
Evaluate("=INDEX(K81:K151,MATCH(TRUE,L81:L151=0,0) )")
End With
End Sub


Regards
Claus B.

Hi Claus

Many thanks for that.

It works really well except I had not realised it was relevant to say
that there is data in A36:E40 and so the macro is writing the values
into C41:F41 and below.

Is there a solution which ignores the presence of the data in A36:E40
and so start writing in C4:F4 (the first empty area above A36:E40) and
then subsequent rows?

Regards
--
Frank