Groups > microsoft.public.excel.programming > #109637

Re: Compare 2 Lists and Take an Action Q

From	Claus Busch <claus_busch@t-online.de>
Newsgroups	microsoft.public.excel.programming
Subject	Re: Compare 2 Lists and Take an Action Q
Date	2016-11-22 10:56 +0100
Organization	A noiseless patient Spider
Message-ID	<o114o4$5p7$1@dont-email.me> (permalink)
References	(14 earlier) <ebb7b8ae-cc8d-403e-b8d1-06eec790cfb0@googlegroups.com> <o10qkm$7lq$1@dont-email.me> <e0fbc4aa-1f08-4f68-b1d0-edb3ef50366e@googlegroups.com> <o11293$tdp$1@dont-email.me> <90b434a5-518d-4365-ba1b-1bb32a61b9ba@googlegroups.com>

Show all headers | View raw

Hi Sean,

Am Tue, 22 Nov 2016 01:35:02 -0800 (PST) schrieb seanryanie@yahoo.co.uk:

> B; D; G, these should be copied to Columns
> A; B; H
> 
> The rows for this 2nd filter should start at Row278, but as explained, this is dynamic, it depends on the length of Filter # 1

try:

Sub ExtractUnmatched()
Dim myCnt As Long, LRow As Long
Dim c As Range

With Sheets("Reconciliation")
    LRow = .Cells(.Rows.Count, "M").End(xlUp).Row
    .Range("M1:Q" & LRow).AutoFilter Field:=5, Criteria1:="="
    myCnt = Application.Subtotal(3, .Range("M:M")) - 1
    If myCnt > 14 Then Sheets("Report").Rows(28).Resize(myCnt - 15).Insert
    .Range("N2:N" & LRow).Copy
    Sheets("Report").Range("B14").PasteSpecial xlPasteValues
    .Range("O2:O" & LRow).Copy
    Sheets("Report").Range("A14").PasteSpecial xlPasteValues
    .Range("P2:P" & LRow).Copy
    Sheets("Report").Range("H14").PasteSpecial xlPasteValues
    .AutoFilterMode = False
    
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set c = Sheets("Report").Range("A:A").Find("Payments",
LookIn:=xlValues, lookat:=xlPart)
    LRow2 = c.Row + 2
    .Range("A1:K" & LRow).AutoFilter Field:=11, Criteria1:="="
    myCnt = Application.Subtotal(3, .Range("A:A")) - 1
    If myCnt > 7 Then Sheets("Report").Rows(LRow2).Resize(myCnt - 7).Insert
    .Range("B2:B" & LRow).Copy
    Sheets("Report").Range("A" & LRow2).PasteSpecial xlPasteValues
    .Range("D2:D" & LRow).Copy
    Sheets("Report").Range("B" & LRow2).PasteSpecial xlPasteValues
    .Range("G2:G" & LRow).Copy
    Sheets("Report").Range("H" & LRow2).PasteSpecial xlPasteValues
    .AutoFilterMode = False
End With

End Sub

I sent you back your workbook.


Regards
Claus B.
-- 
Windows10
Office 2016

Thread

Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 11:17 -0800
  Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 20:38 +0100
    Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 14:46 -0500
      Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:07 +0100
        Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 12:42 -0800
          Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-18 21:51 +0100
            Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:04 -0500
          Re: Compare 2 Lists and Take an Action Q GS <gs@v.invalid> - 2016-11-18 16:05 -0500
            Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-18 13:26 -0800
              Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:09 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 17:22 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 08:33 -0800
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 09:19 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-21 19:05 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 11:39 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 07:47 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-21 22:58 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 08:04 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 00:40 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:14 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 01:35 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 10:56 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 03:28 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:26 +0100
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 13:39 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 05:14 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:29 +0100
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 14:52 +0100
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 11:20 -0800
                Re: Compare 2 Lists and Take an Action Q seanryanie@yahoo.co.uk - 2016-11-22 12:19 -0800
                Re: Compare 2 Lists and Take an Action Q Claus Busch <claus_busch@t-online.de> - 2016-11-22 22:36 +0100
  Re: Compare 2 Lists and Take an Action Q bulong <bulong@gmail.com> - 2016-11-19 09:29 +0000

csiph-web