Avatar billede Daffodil Professor
06. oktober 2016 - 12:08 Der er 3 kommentarer og
1 løsning

Udsøgning og kopi af visse poster

Jeg har et regnark med fem kolonner (A:E), hvor jeg i kolonne E har postnumre. I et andet regneark har jeg en liste over godkendte postnumre.

Jeg har behov for at udsøge de ikke godkendte postnumre. Jeg har prøvet med følgende VBA kode:

Sub KOPI_POST_UKENDT()

'Find postnummer der ikke er i tabel.
Dim a As Variant
    For Each c In Workbooks("PERSONAL.XLSB").Worksheets(7).Range("a:a").Cells
        If c.Value = "" Then Exit Sub
            a = c.Value
            Range("e2:e2").Select
            For Each x In Sheets(1).Range(Selection, Selection.End(xlDown)).Cells
                If x.Value = a Then
                x.EntireRow.Select
                Selection.Copy
                Sheets("Ark3").Activate
                Selection.Insert
                Shift = xlDown
                Sheets("Ark1").Activate
                End If
            Next x
        Next c
End Sub

Denne kodning udsøger og kopiere de godkendte postnumre, men jeg har behov for det modsatte. Jeg troede at man så kunne ændre kodningen i "If x.Value = a Then til "If x.Value <> a Then" men dette duer ikke.

Jeg testkørte med 5 linjer (2 poster skulle findes) og resultater blev 52 kopi linjer hvor der kun skulle have været to .

Er der en der kan hjælpe med løsningen på denne problemstilling.

Efterfølgende skal jeg slette postnummeret i det oprindelige ark, men ikke linjens øvrige oplysninger. Her har jeg ikke fundet en løsning, men er den lige til højrebenet vil jeg da sætte pris på dette også. :-)
Avatar billede excelent Ekspert
06. oktober 2016 - 21:17 #1
Sub test()

Set sh1 = Sheets(1) ' Arket hvor du har alle postnr legale som illegale
Set sh2 = Sheets(2) ' Arket hvor du har alle legale postnr
Set rng1 = sh1.Range("A2:A" & sh1.Cells(10000, "A").End(xlUp).Row) ' alle postnr
Set rng2 = sh2.Range("E2:E" & sh2.Cells(10000, "E").End(xlUp).Row) ' legale postnr

For Each c In rng1
' næste ledige celle i kolonne H Ark2
rk3 = sh2.Range("H" & sh2.Cells(10000, "H").End(xlUp).Row).Row + 1
' illegale postnr skrives i kolonne H Ark2
If Application.CountIf(rng2, c) < 1 Then sh2.Range("H" & rk3) = c
Next

End Sub
Avatar billede Daffodil Professor
07. oktober 2016 - 07:53 #2
Har modificeret lidt i programmet:

Sub test()

Set sh1 = Sheets(1) ' Arket hvor du har alle postnr legale som illegale
Set sh2 = Sheets(2) ' Arket hvor du har alle legale postnr
Set sh3 = Sheets(3) ' Arket der opsamler de udvalgte poster
Set rng1 = sh1.Range("E2:E" & sh1.Cells(10000, "E").End(xlUp).Row) ' alle postnr
Set rng2 = sh2.Range("A2:A" & sh2.Cells(10000, "A").End(xlUp).Row) ' legale postnr

For Each c In rng1
' næste ledige celle i kolonne A Ark3
rk3 = sh3.Range("H" & sh3.Cells(10000, "A").End(xlUp).Row).Row + 1
' illegale postnr skrives i kolonne A i  Ark3
If Application.CountIf(rng2, c) < 1 Then sh3.Range("A" & rk3) = c
Next

End Sub

Jeg har indskudt et målark til dataopsamling og byttet om på A og E i rng 1 og rng2.
Dette udsøger posterne og placere dem i ark3. Jeg har forsøgt at få hele linjen med den udsøgte post over i ark3, men jeg kan ikke se hvorledes jeg får denne linje ind i loopet. Et tillægsspørgsmål: tallet 10000, er det antal linjer den tæller? Hvis denne antagelse er rigtigt hvorledes får man den så til at tælle antal udfyldt linjer i stedet for? Det var det jeg forsøgte med følgende i mit oplæg:

Range("e2:e2").Select
For Each x In Sheets(1).Range(Selection, Selection.End(xlDown)).Cells
Avatar billede excelent Ekspert
07. oktober 2016 - 19:35 #3
Tallet 10000 bevirker at koden starter nede i række 10000 og kører op til den finder
den første (nederste) celle med data, hvis den finder data i fx celle E850 så tildeles
variablen rng1 adressen E2:E850.  Dvs. ar koden efterfølgende gennemløber cellerne E2 til
og med E850

Har rettet koden, så den starter fra oven (række 2) og kører ned til den finder den første
tomme celle. Så du skal sikre dig at der ikke er huller (tomme celler) i dine postnumre
ellers risikerer du at koden ikke gennemløber alle postnumrene.


Sub test2()

Set sh1 = Sheets(1) ' Arket hvor du har alle postnr legale som illegale
Set sh2 = Sheets(2) ' Arket hvor du har alle legale postnr
Set sh3 = Sheets(3) ' Arket der opsamler de udvalgte poster
Set rng1 = sh1.Range("E2:E" & sh1.Cells(2, "E").End(xlDown).Row) ' alle postnr
Set rng2 = sh2.Range("A2:A" & sh2.Cells(2, "A").End(xlDown).Row) ' legale postnr

For Each c In rng1
' næste ledige celle i kolonne A Ark3
rk3 = sh3.Range("H" & sh3.Cells(10000, "A").End(xlUp).Row).Row + 1
' illegale postnr skrives i kolonne A i  Ark3
If Application.CountIf(rng2, c) < 1 Then
sh3.Range("A" & rk3) = c
sh3.Range("B" & rk3) = c.Offset(0, 1)
sh3.Range("C" & rk3) = c.Offset(0, 2)
'Tilføj selv flere efter behov
End If
Next

End Sub
Avatar billede Daffodil Professor
10. oktober 2016 - 08:36 #4
Tak for hjælpen, hvis jeg støder ind i tomme postnummerlinjer vender jeg nok tilbage. :-)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Excel kurser for alle niveauer og behov – find det kursus, der passer til dig

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester