• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Voorwaardelijk op gemaakte cellen blijven niet rood

Status
Niet open voor verdere reacties.

fwalraven

Gebruiker
Lid geworden
30 apr 2006
Berichten
150
Hallo,
Met onderstaande code worden alleen de cellen met dubbel voorkomende getallen in kolom A rood gemaakt.
Deze cellen met de bijbehoren rijen dienen apart bewerkt te worden.
Echter zodra nadat de juiste cellen rood zijn gemaakt en de code verder gaat hebben de rood gekleurde cellen weer hun standaard opmaak.
Met als gevolg dat de unieke cellen ook worden meegenomen in de berekening, wat niet de bedoeling is.

Hoe zorg ik er voor dat de cellen rood blijven?

Frans

Code:
Columns("A:A").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
      With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

[SIZE=4] ''Hier zijn de juiste dubbele voorkomende cellen in kolom A rood gekleurd.[/SIZE]


   .Range("A:M").RemoveDuplicates Columns:=Array(1, 13), Header:=xlNo

  [SIZE=4]''Hier hebben de cellen al weer hun standaard kleur, 
   ''met als gevolg dat zowel de dubbele als unieke cellen worden meegenomen in de berekening.[/SIZE]

   .Cells(1, 14) = WorksheetFunction.CountIf(.Columns("M"), .Cells(1, 13))
    rijen = .Cells(Rows.Count, 1).End(xlUp).Row
    For rij = 2 To rijen
Etc, etc.
 
De code is niet compleet daarnaast mist het voorbeeldbestand.
 
Bijlage met voorbeeld.

Het is de bedoeling dat in kolom A de cellen met waarde:
688551
688552
688553
688555
688559
niet worden meegenomen, nadat de overige dubbele cellen rood gekleurd zijn.
Deze cellen moeten later in een aparte kolom worden geplaatst.

Het verschil is dat de overige cellen in kolom A meerdere keren voorkomen met de waarde in kolom M.
Bv 688504 komt 7 keer voor met kolom M met de waarde 99NT92.

Verder vraag ik met af of de voorwaardelijke opmaak, met een kleur, wel de juiste keuze is.
Misschien is het een optie dat bv. met een hulpkolom de vaker voorkomen cellen de waarde 1 krijgen en de hier boven genoemde de waarde 0?

In blad 2 staat een kopie van het origineel. Als de macro is uitgevoerd kan hij niet meer opnieuw worden gedaan. Alle dubbele zijn dan verwijderd.
DAn kan blad 2 weer naar blad 1 gekopieerd worden
Frans
 

Bijlagen

  • CSV.xlsm
    61,3 KB · Weergaven: 33
Niemand, die hier iets over wil zeggen?

Het kan wel/niet, niet duidelijk genoeg, etc, ect.

Frans
 
Hier zij de cellen al weer standaard kleur. Komt dat door RemoveDuplicates?
Je geeft zelf al het antwoord, als er geen duplicates meer zijn dan ook geen rode cellen.
 
Met 2 gedefinieerde namen om het geheel dynamisch te houden.
Code:
Sub VenA()
  With Sheets("Blad1").Cells(1).CurrentRegion.Resize(, 14)
    .Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"
    .Columns(14).Value = .Columns(14).Value
    .RemoveDuplicates Array(1, 13), xlNo
  End With
End Sub
 

Bijlagen

  • CSV.xlsb
    28,8 KB · Weergaven: 18
@VenA,

Alvast dank voor Uw reactie.

In de nieuwe bijlage worden in kolom N de juiste rijen van 1 of 0 voorzien.
Echter als de code verder gaat dan is het de bedoeling dat de rijen met "nul" in kolom N dan niet meer mee doen.
Deze komen later nog een keer aan de beurt.

In kolom O komen dan hoogstens de cijfers 1,2 of 3 voor.

Nog een probleem:
In het originele bestand wordt de kolom N gevuld met #NAAM?, in plaats van een 1 of nul.

Frans
 

Bijlagen

  • CSV (1).xlsb
    26,9 KB · Weergaven: 38
Iets kleuren, kopiëren, bewerken en handmatig(?) aanpassen klinkt als iets wat in Power Query automatisch zou moet kunnen.

Echter, ik kan niet echt volgen wat je nu precies wilt. Wat is de uitgangslijst, wat is het gewenste resultaat en met name WAAROM moet een bepaalde rij wel of niet worden opgenomen.
 
Mij is het ook niet echt helder. Mogelijk

Code:
Sub VenA()
  With Sheets("Blad1")
    .UsedRange.Clear
    .Cells(1).Resize(Sheets("Blad2").Cells(1).CurrentRegion.Rows.Count, 13) = Sheets("Blad2").Cells(1).CurrentRegion.Value
    With .Cells(1).CurrentRegion.Resize(, 15)
      .Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"
      .Columns(14).Value = .Columns(14).Value
      .RemoveDuplicates Array(1, 13), xlNo
      .Columns(14).SpecialCells(2).Offset(, 1).FormulaR1C1 = "=COUNTIFS(Kolom_M,RC[-2],Kolom_N,1)" 'of "=IF(RC[-1]=0,0,COUNTIFS(Kolom_M,RC[-2],Kolom_N,1))"
    End With
  End With
End Sub

Het plaatsen van formules middels VBA is niet het meest efficiënt. Maar eerst maar eens kijken naar wat het uiteindelijke doel is.
 
@VenA,

Deze code geeft ook #NAAM? in kolom O.

Hopelijk is onderstaande tekst duidelijk genoeg.
Tekst staat ook op blad 1 en 2 van het bijgevoegde bestand.

In voorbeeld kolom P staan de juiste getallen,
die behoren ook zo in kolom O te komen.
In deze kolom komen de getallen 4 en 5 niet voor en dat is ook de bedoeling

De in kolom M vermelde tekst staan voor een bepaalde locatie
In kolom A, behorende bij 99NT92 in kolom M, staan meerdere ,3, verschillende nummers.
Zoals 688504, 688505 en 688506.

Deze nummers behoren bij een dagdeel, ochtend, middag of avond.
Waarbij het hoogste nummer bij de ochtend hoort, middelste bij de middag en laagste avond.

Later worden kolom M en O samengevoegd. Zoals 99NT92/3, 99NT92/2 en 99NT/1.
Omdat 688551, 688552, 688553, 688555 en 688559 slecht 1 maal voorkomen
bij een locatie in kolom M mogen deze nu niet meedoen voor bepaling van het dagdeel.

Doen ze wel mee dan geeft dat de getallen 4 en 5, en we hebben maar 3 dagdelen

688551, 688552, 688553, 688555 en 688559 worden later apart verwerkt.

Frans
 

Bijlagen

  • CSV (1).xlsb
    29,6 KB · Weergaven: 17
Ik heb de in rood gekleurde tekst in de code overgenomen van een door VenA geplaatste code vandaag.

In de bijlage werkt dit goed. Kolom O wordt nu op de juiste manier voorzien van 1,2 of 3.
Echter de verwijderde rijen met "0" in kolom N zijn nu totaal verwijderd.
Deze rijen heb ik op een later moment ook nodig.

Kan dit op de een of ander manier ondervangen worden?

Een ander probleem, in het originele bestand komt er in kolom N #NAAM? te staan ipv van "1" of "0"

Frans

Code:
Sub VenA()

  With Sheets("Blad1").Cells(1).CurrentRegion.Resize(, 14)
    .Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"
    .Columns(14).Value = .Columns(14).Value
    .RemoveDuplicates Array(1, 13), xlNo
          
 [COLOR="#FF0000"]With Sheets("blad1").Cells(1).CurrentRegion
 .AutoFilter 14, "0"
        .Offset(1).EntireRow.Delete
    .AutoFilter
      End With[/COLOR]  
      
      
      .Range("A:N").RemoveDuplicates Columns:=Array(1, 14), Header:=xlNo
      .Cells(1, 15) = WorksheetFunction.CountIf(.Columns("M"), .Cells(1, 13))
    rijen = .Cells(Rows.Count, 1).End(xlUp).Row
    For rij = 2 To rijen
        .Cells(rij, 15) = IIf(.Cells(rij, 13) = .Cells(rij - 1, 13), .Cells(rij - 1, 15) - 1, WorksheetFunction.CountIf(.Columns("M"), .Cells(rij, 13)))
   Next rij
  End With
 

Bijlagen

  • CSV (2).xlsb
    29,4 KB · Weergaven: 16
Ik ben eruit, misschien niet de meest elegante oplossing maar het werkt wel in de voorgaande als bijlage mee gestuurde bestanden.
Kort gezegd:
kopieer het origineel naar blad 2. Zie rode tekst in code

De overige code verwijderd rijen met "0" in kolom N.
En in kolom O worden de juiste getallen 1,2 of 3 geplaatst.

Kan dus later blad2 terug kopieren naar blad1 om dan de verwijderde rijen te bewerken.

Blijft alleen staan #NAAM? in hte originele bestand.
Hiervoor open ik een nieuw topic

Frans

Code:
''Kopieert eerst het origineel naar blad2, kan dus later terug gehaald worden.

[COLOR="#FF0000"]
With Sheets("blad1").Cells(1).CurrentRegion
  Sheets("blad1").Columns("A:M").Copy (Sheets("blad2").Range("A1"))
  End With
[/COLOR]

  With Sheets("blad1").Cells(1).CurrentRegion.Resize(, 14)
    .Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"
    .Columns(14).Value = .Columns(14).Value
    .RemoveDuplicates Array(1, 13), xlNo
    
   '' Verwijderd rijen met "0" in kolom N, deze rijen worden later apart bewerkt.
   
 .AutoFilter 14, "0"
      .Offset(1).EntireRow.Delete
    .AutoFilter
   
   
     .Range("A:N").RemoveDuplicates Columns:=Array(1, 13), Header:=xlNo
      .Cells(1, 15) = WorksheetFunction.CountIf(.Columns("M"), .Cells(1, 13))
 
    rijen = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For rij = 2 To rijen
        .Cells(rij, 15) = IIf(.Cells(rij, 13) = .Cells(rij - 1, 13), .Cells(rij - 1, 15) - 1, WorksheetFunction.CountIf(.Columns("M"), .Cells(rij, 13)))
      Next rij
      End With
End Sub
 
Heb je de gedefinieerde namen Kolom_A en Kolom_M wel overgenomen in jouw originele bestand?
 
@VenA

Tabel is een kopie van het origineel.
Alleen minder rijen. Origineel is ongeveer 700 rijen.

Code:
With Sheets("blad1").Cells(1).CurrentRegion.Resize(, 14)

'' Tot hier alles nog goed

    .Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"

''Hier staat #NAAM? in kolom N. Wat "1" of "0" had moeten zijn.

    .Columns(14).Value = .Columns(14).Value
    .RemoveDuplicates Array(1, 13), xlNo

Maar ik zal alles nog eens een keer op een rijtje zetten.

Frans
 
In beide bestanden komt als functie:

=N(AANTALLEN.ALS(Kolom_A;A1;Kolom_M;M1)>1)

in kolom N te staan??

Frans
 
#NAAM? Probleem

Hallo,

In het bijgevoegde bestand doet de macro wat hij doen moet.

De tabel is een kopie van het originele bestand.

In kolom N komt de waarde "1" te staan als in kolom A een waarde meerdere malen voorkomt. Bv 688504, etc ect.
Komt een waarde slechts 1x voor, dan komt er "0" te staan in kolom N, in de bijlage zijn dit 688559. 68855, 688553, 688552 en 688551
Daarna komen in kolom O de waarde 1,2,of 3 te staan. Deze getallen staan voor een dagdeel.

Echter in het originele bestand komt met de dezelfde macro ipv "1" of "0" #NAAM? in kolom N te staan.

Code:
.Columns(14).FormulaR1C1 = "=N(COUNTIFS(Kolom_A,RC[-13],Kolom_M,RC[-1])>1)"
In deze regel gaat het fout in het originele bestand

Rara hoe kan dit?

Frans
 

Bijlagen

  • CSV (1).xlsb
    32 KB · Weergaven: 21
@Silverster-ponte,

Waarom werkt de macro dan wel goed in het bijgevoegde bestand?

@AD1957,

Omdat de voorwaardelijke opmaak met kleur al helemaal niet meer van toepassing is.
En de status opgelost had gegeven
In #12 al aan gegeven heb dat voor het probleem met #NAAM? een nieuw topic te openen.

Frans
 
Laatst bewerkt:
Dan heb je #13 niet gelezen of niet begrepen.
klick in het Lint op formules en dan op namen beheren.
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan