Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #107923 > unrolled thread

Re: Macro to paste from clipboard

Back to article view | Back to microsoft.public.excel.programming

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Macro to paste from clipboard Claus Busch <claus_busch@t-online.de> - 2015-08-01 11:09 +0200
    Re: Macro to paste from clipboard F <news@nowhere> - 2015-08-03 10:52 +0100
      Re: Macro to paste from clipboard Claus Busch <claus_busch@t-online.de> - 2015-08-03 12:07 +0200
        Re: Macro to paste from clipboard Claus Busch <claus_busch@t-online.de> - 2015-08-03 12:12 +0200
      Re: Macro to paste from clipboard Claus Busch <claus_busch@t-online.de> - 2015-08-03 12:20 +0200
        Re: Macro to paste from clipboard F <news@nowhere> - 2015-08-03 12:01 +0100
          Re: Macro to paste from clipboard Claus Busch <claus_busch@t-online.de> - 2015-08-03 13:12 +0200

#107923 — Re: Macro to paste from clipboard

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$151),$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.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [next] | [standalone]

#107931

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$151),$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

[toc] | [prev] | [next] | [standalone]

#107932

Hi Frank,

Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:

> 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?

then you have to hardcode the target cell every day.
My suggestion: Delete C4:F40 first

With ActiveSheet
    .Range("C4:F40").ClearContents
    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$151),$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


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#107933

Hi Frank,

Am Mon, 3 Aug 2015 12:07:47 +0200 schrieb Claus Busch:

>     .Range("C4:F40").ClearContents

ignore the previous post. You cannot delete every time you run the
macro. Delete that range manually before first run.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#107934

Hi again,

Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:

> 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?

if you run the macro every day you could create the row with the day:
Set dest = .Cells(Day(Date) + 3, 3)


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#107935

On 03/08/2015 11:20, Claus Busch wrote:
> Hi again,
>
> Am Mon, 3 Aug 2015 10:52:48 +0100 schrieb F:
>
>> 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?
>
> if you run the macro every day you could create the row with the day:
> Set dest = .Cells(Day(Date) + 3, 3)
>
>
> Regards
> Claus B.

Hi

And thanks again.

Trying hard to educate myself... would
Set dest = Range("C36").End(xlUp)(2)
do what I want?

Regards
-- 
Frank

[toc] | [prev] | [next] | [standalone]

#107936

Hi Frank,

Am Mon, 3 Aug 2015 12:01:17 +0100 schrieb F:

> Trying hard to educate myself... would
> Set dest = Range("C36").End(xlUp)(2)
> do what I want?

not if you have data in C1:C40.
Then it will find C41


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web