2 rijen Excel vergelijken op exacte waarde en volgorde VBA

[lanieuwe]

Mijn vraag

Ik heb 2 rijen in Excel met dezelfde waarden. Bijvoorbeeld de eerste rij waarden: cel A1 = Piet, B1 = Kees en C1 = Jan.
De tweede rij waarden: cel A1 = Piet, B1 = Kees en C1 = Jan.

Indien bovenstaande het geval is dan hoeft er niets te gebeuren.
Echter als in rij 1 een waarde veranderd bijvoorbeeld: De waarde in cel A1 wordt Kees dan moet er een melding verschijnen met een melding MsgBox "Niet goed". (Volgorde is dus ook van belang).

Hoe moet de code eruit zien?

Alvast dank!

Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
voorwaardelijke opmaak. dit werkt maar ik wil dit via een melding. Vandaar VBA.

[dix-neuf]

Dat kan:
1. met gegevensvalidatie, waarbij je vergelijkt of A2=A1, B2=B1, en C2=C1 en bij geen overeenkomst een foptmelding laat verschijnen.
2. met een 'Private Sub Worksheet_Change(...)-macro waarin je dezelfde vergelijkingen maakt en bij geen overeenkomst een messagebox met het gewenste foutbericht laat weergeven..

[F_J_K]

Ik zou het zonder code doen. Aantal.als() moet van beide gelijk zijn. Toon verschillen aan via voorwaardelijke opmaak.

En bedenk dat het bij wijzigen altijd tijdelijk verschilt. Dan wil je niet lastig worden gevallen met een msgbox.

[naitsoezn]

lanieuwe schreef op zondag 19 april 2020 @ 11:26:

Hoe moet de code eruit zien?


Wat ik al gevonden of geprobeerd heb
voorwaardelijke opmaak. dit werkt maar ik wil dit via een melding. Vandaar VBA.

Het is makkelijker om te helpen als je weet waar je op vast loopt. Dit neigt naar een script-request . Dus hoe ziet je huidige VBA-code eruit en waarom werkt dit niet?

[lanieuwe]

Bedankt allemaal voor het reageren.
Ik werk inderdaad met een private sub worksheet change statement.
Voorwaardelijke opmaak werkt ook maar wil graag met een Msgbox werken gezien het om veel cellen gaat in verschillende worksheets (Dat ga ik met for next oplossen). de gebruiker moet namelijk een aantal cellen kopieren uit een ander programma en .plakken in de Excel. Die cellen hebben allemaal een bepaalde waarde en volgorde. als die na het plakken niet kloppen moet er een melding komen. Maar ik heb het voorbeeld even eenvoudig gehouden:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") <> Range("A2") Then
MsgBox "Niet goed"
ElseIf Range("B1") <> Range("B2") Then
MsgBox "Niet goed"
ElseIf Range("C1") <> Range("C2") Then
MsgBox "Niet goed"
End If
End Sub

Bovenstaande werkt wel maar lijkt me ook traag als je veel cellen met elkaar moet vergelijken.
Hoe zouden jullie dit doen met VBA?

[F_J_K]

Ik zou -als per se VBA- dezelfde countif gebruiken in een loop Application.WorksheetFunction.Countif( ...

Maar nogmaals; bedenk dat de melding gaat komen bij -iedere- wijziging. Immers:

Nu heb je
A B
B A
C C

en je wilt naar

A B
B A
B B

dan heb je tijdelijk
A B
B A
B C
(alert!alert!alert! )

edit: * F_J_K is dom. Zie F_J_K in "2 rijen Excel vergelijken op exacte waarde en volgorde VBA"

[ Voor 18% gewijzigd door F_J_K op 20-04-2020 13:20 ]

[lanieuwe]

gegevens validatie zou ik kunnen proberen. Echter wordt de voorwaarde dan niet overschreven als de gebruiker in desbetreffende cel plakt?. Ik kan ze niet opvoeden om alleen waarden te plakken.

[F_J_K]

Ja, ze moeten dan dus plakken-als-waarde. Of je zet de controles in hulpkolommen die je beschermd tegen overschrijven.

Ook met VBA kan je niet vertrouwen op de eindgebruiker, immers kan die dan in de verkeerde cellen plakken, kan rijen/kolommen toevoegen of verwijderen, etc.

[lanieuwe]

Kun je een voorbeeld geven welke formule ik moet invoeren bij een cel van een hulpkolom?

[dix-neuf]

F_J_K schreef op zondag 19 april 2020 @ 13:26:
bedenk dat het bij wijzigen altijd tijdelijk verschilt. Dan wil je niet lastig worden gevallen met een msgbox.

Als je bij gegevensvalidatie als 'stijl' "Stoppen" kiest, kan er geen verschil zijn, want andere invoer dan wat je voorschrijft in de validatie is dan niet mogelijk. Een bericht verschijnt er wel.

lanieuwe schreef op zondag 19 april 2020 @ 20:29:
gegevens validatie zou ik kunnen proberen. Echter wordt de voorwaarde dan niet overschreven als de gebruiker in desbetreffende cel plakt?

Nee, zie mijn reactie op bericht van F_J_K hierboven.
Voorbeeld van gegevensvalidatie voor B1:
Toestaan: "Aangepast"; Formule: =A1
Tab 'Foutmelding', aanvinken: 'Foutmelding weergeven...'; Stijl: Stoppen; Foutbericht: Niet juist!

Voorbeeld als je toch een macro wil gebruiken:

code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Or([a1] <> [a2], [b1] <> [b2], [c1] <> [c2]) Then
MsgBox "Niet juist!"
End If
End Sub

NB: Je moet uiteraard niet beide (validatie en macro) tegelijkertijd gebruiken.

[lanieuwe]

Even terugkomend op de gegevensvalidatie.
Als je een cel zonder validatie kopieert en plakt over een cel heen
mèt validatie, verdwijnt de validatie. Net zoals met formules. Kun je de cel zo beschermen zodat de formule of validatie actief blijft, maar dat de gebruiker wel kan plakken? Plakken van alleen slechts de waarden kan ik niet afdwingen bij de gebruiker.

[F_J_K]

Mogelijk kan je iets als https://stackoverflow.com...e-behavior-when-pasting-o gebruiken (let op de taal, die code zal alleen werken in een EN Excel)

Maar zoals gezegd: makkelijker is de gegevensvalidatie doen niet in de te kopieren cel, maar in een hulpkolom ernaast.

Edit:

dix-neuf schreef op zondag 19 april 2020 @ 21:27:
[...]
Als je bij gegevensvalidatie als 'stijl' "Stoppen" kiest, kan er geen verschil zijn, want andere invoer dan wat je voorschrijft in de validatie is dan niet mogelijk. Een bericht verschijnt er wel.

Dan kan er nooit een wijziging handmatig worden gedaan (want de tussen-fase voldoet per definitie niet aan dat links en rechts gelijke aantallen hebben). En het lijkt me expliciet wel de bedoeling dat men de lijsten kan wijzigen, anders is een controle van de wijzigingen niet nodig

[ Voor 47% gewijzigd door F_J_K op 20-04-2020 11:15 ]

[lanieuwe]

Ik krijg het in die "hulpkolom" niet gelukt.
gegevensvalidatie gaat toch altijd om desbetreffende cel?
Kun je misschien een simpel voorbeeldje geven?

[lanieuwe]

Aanvulling.
gegevensvalidatie werkt gek genoeg niet op formules.

Wat heb ik gedaan:
In cel B1 is de hulp cel. Hier heb ik de formule =A1 geplaatst.
Gegevenvalidatie heb ik ook op B1 ingesteld op namelijk formule in gegevensvalidatie B1=C1.
A1 is het veld waar geplakt wordt.

Dit werkt bij mij niet.Gek genoeg als cel B1 wijzigt als ik in cel A1 typ, dan krijg ik geen melding van de validatie.

[dix-neuf]

F_J_K schreef op maandag 20 april 2020 @ 11:09:
Dan kan er nooit een wijziging handmatig worden gedaan (want de tussen-fase voldoet per definitie niet aan dat links en rechts gelijke aantallen hebben). En het lijkt me expliciet wel de bedoeling dat men de lijsten kan wijzigen, anders is een controle van de wijzigingen niet nodig

Er kunnen wel wijzigingen plaatsvinden maar slechts die wijziging(en) die in de validatie werden ingesteld/toegestaan. Dat is ook de bedoeling van de validatie lijkt mij.

[Lustucru]

lanieuwe schreef op maandag 20 april 2020 @ 11:52:
Aanvulling.
gegevensvalidatie werkt gek genoeg niet op formules.

Wat heb ik gedaan:
In cel B1 is de hulp cel. Hier heb ik de formule =A1 geplaatst.
Gegevenvalidatie heb ik ook op B1 ingesteld op namelijk formule in gegevensvalidatie B1=C1.
A1 is het veld waar geplakt wordt.

Dit werkt bij mij niet.Gek genoeg als cel B1 wijzigt als ik in cel A1 typ, dan krijg ik geen melding van de validatie.

Het idee lijkt me ook niet om op de hulpkolom gegevensvalidatie toe te passen, maar die te gebruiken als een duidelijke indicator of de data correct zijn.

Dus: in een cel heb je je controleformule (bv' =A1=A2') en daar zet je dmv voorwaardelijke opmaak een alert op dat hij bv rood wordt als het resultaat onwaar is. @F_J_K heeft al duidelijk aangegeven dat een msgbox als foutafhandeling bij invoerfouten terecht achterhaald is: de gebruiker moet de kans krijgen om de fout te herstellen zonder eerst ~tig messageboxen weg te hoeven klikken.

Met vba kun je bereiken dat de cel zelf rood wordt -of als je persé wil toch die vervelende alertbox-, maar je code slaat idd een belangrijk stuk over:

lanieuwe schreef op zondag 19 april 2020 @ 19:35:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") <> Range("A2") Then
MsgBox "Niet goed"
ElseIf Range("B1") <> Range("B2") Then
MsgBox "Niet goed"
ElseIf Range("C1") <> Range("C2") Then
MsgBox "Niet goed"
End If
End Sub

Bovenstaande werkt wel maar lijkt me ook traag als je veel cellen met elkaar moet vergelijken.
Hoe zouden jullie dit doen met VBA?

Je vergeet eerst het resultaat te beperken tot de doorsnede van rij 1:1 met de gewijzigde cellen. Dat kan met interset. Vervolgens loop je door alle cellen in de doorsnede heen en controleer je of die gelijk is aan de referentiewaarde. Die kleur je rood en aan het eind geef je één messagebox.

Dat ziet er ongeveer zo uit (let op: code zelf afmaken, is meer een voorbeeld dan echte code en er zitten ongetwijfeld syntaxfouten in...)

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
dim ...
    rangeDoorsnede=intersect(Target,range("1:1"))
    for each rangeCel in rangeDoorsnede
        if rangeCel.value<>rangeCell.Offset(1,0) then
             errCount=errCount+1
             rangecell.interior.backcolor = vbRed
         end if
    next

     If errCount>0 then msgbox "Er zijn " & errCount & "fout(en) geconstateerd"
End (pseudo)sub

[ Voor 11% gewijzigd door Lustucru op 20-04-2020 13:05 . Reden: tussenposter ]

[F_J_K]

dix-neuf schreef op maandag 20 april 2020 @ 12:43:
[...]
Er kunnen wel wijzigingen plaatsvinden maar slechts die wijziging(en) die in de validatie werden ingesteld/toegestaan. Dat is ook de bedoeling van de validatie lijkt mij.

Het is OK als beide cellen 'Piet' als waarde hebben. Op een gegeven moment wil men blijkbaar Piet vervangen door Henk. Dan is het ok als in beide cellen 'Henk' staat. Maaaarrrr het duurt langer dan nul seconden om in beide cellen op delete te drukken en dan Henk in te typen. In die ene microseconde die dat typen kost staat in de ene cel Piet en in de andere Henk. Computer says no

offtopic:
Dat zei ik in F_J_K in "2 rijen Excel vergelijken op exacte waarde en volgorde VBA" ook maar gebruikte daar ten onrechte kolommen ipv rijen en erger: ik had in mijn hoofd dat de volgorde niet uitmaakt waardoor de voorbeelden onzinnig waren My bad.

[lanieuwe]

dim rangedoorsnede as range
Hoe declareer ik rangecell?

[Lustucru]

Ook als range.

[lanieuwe]

Dit doet helemaal niets eigenlijk.

Hieronder mijn code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rangedoorsnede As Range
Dim rangecell As Range

rangedoorsnede = Intersect(Target, Range("1:1"))
For Each rangecell In rangedoorsnede
If rangecell.Value <> rangecell.Offset(1, 0) Then
errCount = errCount + 1
rangecell.Interior.BackColor = vbRed
End If
Next

If errCount > 0 Then MsgBox "Er zijn " & errCount & "fout(en) geconstateerd"
End Sub

[F_J_K]

Vreemd want het zou soms een foutmelding moeten geven.

Ik zou beginnen met stoppen als het, hier, niet regel 1 betreft:
If Intersect(Target, Range("1:1")) Is Nothing Then Exit Sub

Ook is m.i. de loop niet nodig: immers verandert bij change() toch maar 1 cel? Dan voldoet het om die ene cel te checken. Of als je wilt in een loop alle cellen op regel 1, maar ook dat behoeft dan geen intersect.

Dan resteert If Target.Value <> Target.Offset(1, 0) Then Target.Interior.ColorIndex = 3
Of mooier ook een else met andere kleur (weer wit als OK).

[Lustucru]

lanieuwe schreef op dinsdag 21 april 2020 @ 12:55:
Dit doet helemaal niets eigenlijk.

Hij moet op zijn minst compileer-fouten geven, want er zitten nog een paar fouten in:
- je gebruikt blijkbaar geen option explicit, want errount wordt nergens gedeclareerd;
- de toewijzing van een object moet voorafgegaan worden door 'set', i.e. Set rangeDoorsnede= ' etc
- interior heeft geen eigenschap BackColor; dat heet gewoon Color.

Voor alle duidelijkheid. Neem nooit code over van het internet zonder zelf te begrijpen wat de code doet.

Dat ziet er ongeveer zo uit (let op: code zelf afmaken, is meer een voorbeeld dan echte code en er zitten ongetwijfeld syntaxfouten in...)

De code in een losse module gezet ipv in het werkblad? Zet eens een breakpoint en kijk of de code wordt aangeroepen en ga er dan stap voor stap doorheen, en compileer altijd je code handmatig [foutafhandeling->compileer]

F_J_K schreef op dinsdag 21 april 2020 @ 13:19:
Ik zou beginnen met stoppen als het, hier, niet regel 1 betreft:
If Intersect(Target, Range("1:1")) Is Nothing Then Exit Sub

Dat, of het werkzame deel van de macro achter een if clausule. If not intersect() is Nothing then ... Endif. Anders krijg je idd meteen een fout op de lus zelf.

Ook is m.i. de loop niet nodig: immers verandert bij change() toch maar 1 cel? Dan voldoet het om die ene cel te checken. Of als je wilt in een loop alle cellen op regel 1, maar ook dat behoeft dan geen intersect.

Dat is niet waar. Als je 10 cellen plakt wordt worksheet_change één keer getriggerd met een targetrange van 10 cellen,

Dan resteert If Target.Value <> Target.Offset(1, 0) Then Target.Interior.ColorIndex = 3
Of mooier ook een else met andere kleur (weer wit als OK).

Die moet er idd er idd ook nog bij, plus wat foutafhandeling.

[ Voor 49% gewijzigd door Lustucru op 22-04-2020 08:13 . Reden: er stond iets stoms ]

[F_J_K]

Lustucru schreef op woensdag 22 april 2020 @ 07:55:
Dat is niet waar. Als je 10 cellen plakt wordt worksheet_change één keer getriggerd met een targetrange van 10 cellen,

Je hebt gelijk, natuurlijk

Al is dan de vraag of niet de hele regel 1:1 wordt geplakt, zodat geen intersect nodig is een en simplere 'gewone' loop voor alle gevulde kolommen voldoen.

[Lustucru]

Zie: F_J_K in "2 rijen Excel vergelijken op exacte waarde en volgorde VBA" Ook @F_J_K gaat er van uit dat er meer wijzigingen kunnen zijn dan alleen het plakken in rij 1.