[XL2013] ALS-formule een macro laten uitvoeren

[NielsjeNL]

Voor een opdracht moet ik in Excel een aantal spreadsheets vullen. Dit wil ik grotendeels automatiseren (handmatig 50+ spreadsheets veranderen met 10 werkbladen waar ongeveer 270 rijen in staan is me toch iets te veel ). Een hoop is me al gelukt via de macro recorder, maar er is één gigantisch ding waar ik op vast zit; het aanroepen van een macro via een =ALS formule in een cel.

De ALS formule: =ALS(I11="";"";RunMacro(FillCell))
De FillCell macro, samen met de functie:

code:

Function RunMacro(FillCell)
Application.Run "PERSONAL.XSLB!FillCell"
End Function
_________________________
Sub FillCell()
'
' FillCell Macro
'

'
    With ActiveCell
    Range(Cells(.Row, "A"), Cells(.Row, "K")).Interior.Color = RGB(0, 255, 125)
    End With
End Sub

Dit is de bedoeling (voorbeeld);
(Alle macro's staan in Personal.xslb, dit omdat ik anders per bestand de macro moet importeren)
In K3 staat een ALS formule die kijkt of cel I3 data bevat.
Als cel I3 gewijzigd wordt en data gaat bevatten, is het de bedoeling dat de rij van de cel een andere kleur krijgt d.m.v. een macro (ik heb gekeken naar conditionele formatting maar voor zover ik het snap moet dit per rij aangemaakt worden, en dat is niet mogelijk) en er een kruisje komt te staan in cel K3.
Als de data uit I3 wordt verwijderd moet de cel weer leeg worden.
Het verwijderen en het kruisje kijk ik later naar, dat is nu geen prioriteit.

Ik heb op internet gelezen dat je dit moet aanroepen via een functie, omdat alle macro's in een ander bestand staan. Dit heb ik geprobeerd met Application.Run "PERSONAL.XSLB!FillCell", in een functie die in hetzelfde bestand staat als FillCell (moet de functie in de werkboek zelf staan?), maar als ik cel I3 dan vul komt er in cel K3 #NAAM? te staan. Dit is het punt waar ik op vastloop, aangezien ik zelf vrij weinig kennis heb van programmeren, en al helemaal geen kennis van VBA.

Zou de functie in een andere plek moeten komen te staan, of klopt deze?
Is de functie uberhaupt correct?

[Fasdick]

Dit probleem heb ik ook.
Bij het invoeren van een naam moet de functie zoeken of in een andere werkblad van dezelfde werkmap die naam al voorkomt. Is dat zo dan moet die naam met verdiende punten in het andere werkblad worden opgenomen. Dat werkt.
Als de naam nog niet voorkomt op het andere werkblad dan moet die op de eerste lege rij in dat blad worden gekopieerd met de verdiende punten. Ik krijg dan de mededeling "Verwerk".
Klik ik op de daarbij behorende macroknop dan gebeurt dat. Tot zover werkt het ook.
De functie luidt: =ALS(B7="";"";(ALS.FOUT(VERT.ZOEKEN(B7;Uitslagen!$A$8:$X$256;1;ONWAAR);"Verwerk")))

Nu wil ik graag dat ik niet op de knop "Verwerk" hoef te klikken, maar dat dat automatisch gebeurt. Ik kan niet ontdekken hoe ik dat moet oplossen.

Is er iemand die de oplossing weet?

[dix-neuf]

NielsjeNL schreef op woensdag 12 februari 2014 @ 11:01:
In K3 staat een ALS formule die kijkt of cel I3 data bevat.
Als cel I3 gewijzigd wordt en data gaat bevatten, is het de bedoeling dat de rij van de cel een andere kleur krijgt d.m.v. een macro (ik heb gekeken naar conditionele formatting maar voor zover ik het snap moet dit per rij aangemaakt worden, en dat is niet mogelijk) en er een kruisje komt te staan in cel K3.

Als de voorwaarde om een rij te kleuren het al-dan niet leeg zijn van cel i3 (en eventueel ook onderliggende cellen) is, dan is voorwaardelijke opmaak eenvoudig in te stellen zonder macro, voor zoveel rijen als je maar wil (via isleeg(cel(len))

Als de data uit I3 wordt verwijderd moet de cel weer leeg worden.

Als je uit een cel de gegevens verwijdert, dan is die cel automatisch leeg.

Vreemd vind ik dat je een formule (in K3) vervangt door een kruisje. Dat kan natuurlijk wel, maar het betekent dat die formule maar beperkt geldig is.

[dixet]

Beter open je een nieuw topic in plaats van een 8 (!) jaar oud topic te kapen

Maar wat je hier moet doen is niet de macro via een functie aanroepen, dat werkt alleen bij macro-functies die een waarde teruggeven die je in die cel wilt tonen.

Je kan hier beter de Worksheet_Change gebeurtenis gebruiken om automatisch je macro uit te voeren wanneer je een naam invult in je eerste tabblad

[Fasdick]

Sorry, ben net 1 dag gebruiker van Tweakers. Zoek al weken naar een oplossing. Ben geen crack in Excel, maar wel ervaring. Doe het vermoedelijk altijd veel te moeilijk.
Ik heb het volgende gevonden:
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("B7:V80")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
MsgBox "Success"
End If
End Sub

En dat werkt.
Maar in plaats van de message box wil ik de macro aanroepen. De macro is:
ub Vulcel()
'' Vulcel Macro
'
ActiveCell.Copy
Sheets("Uitslagen").Select
Range("A65535").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Sheets("Formulier daguitslag").Select
Range("B7:X80").Select
ActiveCell.Select

End Sub

Het aanroepen (callen) van die macro lukt mij niet. Kan je mij vertellen hoe ik dat moet opschrijven. Zou zeer dankbaar zijn.

[Fasdick]

De macro is niet ub Vulcel, maar Sub Vulcel ()

[Fasdick]

Ik probeer:

Call Macro Vulcel en
Call MacroVulcel en
Call Macro Vulcel() en
Call MacroVulcel(),
maar dan krijg ik:
Compileerfout
Verwacht instructie einde

[dixet]

Fasdick schreef op maandag 25 april 2022 @ 16:50:
Ik probeer:

Call Macro Vulcel en
Call MacroVulcel en
Call Macro Vulcel() en
Call MacroVulcel(),
maar dan krijg ik:
Compileerfout
Verwacht instructie einde

Bijna goed 👍, je moet alleen het woordje"macro" weg laten
Dus

code:

Call Vulcel()

[Fasdick]

Dankjewel. Zo werkt de macro.

Alleen nu loop ik tegen het probleem dat ik de controle op nieuwe of bestaande namen kwijt ben.

Ik heb namelijk in controlecellen het volgende staan:
=ALS(B7="";"";(ALS.FOUT(VERT.ZOEKEN(B7;Uitslagen!$A$7:$X$47;1;ONWAAR);"Verwerk")))
Dan druk ik op de macroknop "verwerk" en als die dan een bestaande naam vindt zet die daarachter de uitslag van die dag. Vindt die die naam niet, dan maakt hij een nieuwe aan op de eerste lege regel van het blad met de uitslagenverzameliing en zet daar de punten van die dag bij.

Gebruik ik ook de programmacode, dan komen in het blad met de uitslagen de namen ook al bestaan ze al erbij met de punten 2x.

Er is dus een conflict tussen beide opdrachten.

Mag ik je de werkmap zonder de programmacode zenden?

De code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B7:V80")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
Call Vulcel
End If
End Sub

[Lustucru]

Fasdick schreef op maandag 25 april 2022 @ 23:54:

[...]
Alleen nu loop ik tegen het probleem dat ik de controle op nieuwe of bestaande namen kwijt ben.
[...]

En je gaat ook merken dat alles wat je wijzigt in de range B7:V80 wordt toegevoegd. Controle is niet zo moeilijk: dat doe je mbv range.find(). Iets als If sheets($).range.find(what:=$) is nothing then

Vervolgens kun je de macro zelf optimaliseren door al die selects eruit te halen. DAn blijft er nog één regel over en die kun je net zo goed in het onchange.event zelf plaatsen.

[Fasdick]

Iedereen dank voor het meedenken.

In de sheet, waarin ik de daguitslag invoer heb ik op celniveau een controlefunctie.
Die kijkt of in de doorlopende lijst met uitslagen de naam van de toegevoegde speler al voorkomt.
Zo ja, dan wordt achter de naam van die speler voor de betreffende speeldag het puntenaantal gezet.
Zo nee, dan moet ik op een macroknop klikken en dan wordt in de lijst met uitslagen de nieuwe naam gezet met vermelding van de punten.

Nu wil ik die handeling (klikken op macroknop) overslaan en de controle en het resultaat daarvan volledig automatisch laten verlopen, zodat ik alleen maar de naam en ENTER hoef in te voeren.
De programmacode komt niet aan de beurt doordat de controlefunctie
=ALS(B7="";"";(ALS.FOUT(VERT.ZOEKEN(B7;Uitslagen!$A$7:$X$47;1;ONWAAR);"Verwerk")))
om de verwerking vraagt en vraagt dus om op de macroknop te drukken.

Als ik de controlefunctie uitschakel zet de programmacode alleen maar een resultaat in de uitslagen als de naam er al staat. Maar nieuwe namen worden dan niet toegevoegd.

Het is dus een conflict tussen werken op celniveau met tegelijk werken met de programmacode, die op werkbladniveau werkt.

De voorgestelde zoekfunctie lost dat probleem niet op.

[Lustucru]

Dat je iets niet snapt wil nog niet zeggen dat het niet de oplossing is.

Je vraag is wel duidelijk en de oplossing staat een post boven je...

Anyway: je kunt in een functie die aangeroepen wordt in een formule nooit schrijven naar een cel. Je moet dus alles afhandelen in de routine(s) onder worksheet_change()

[ Voor 36% gewijzigd door Lustucru op 26-04-2022 12:18 ]

[EjjE101]

waarom maak je niet een custom functie?

noem je hem Public Function BLA () dan kun je hem in een cell aanroepen met =BLA

[Lustucru]

EjjE101 schreef op dinsdag 26 april 2022 @ 12:23:
waarom maak je niet een custom functie?

noem je hem Public Function BLA () dan kun je hem in een cell aanroepen met =BLA

Omdat dat dus niet werkt. Een UDF kan geen data wegschrijven.

[Fasdick]

Lustrucu,
Ik snap het inderdaad niet, maar ik kan de door jou voorgestelde oplossing niet invoeren. Ik krijg die range.find() niet goed geformuleerd in mijn worksheet. Ik heb daar ook geen ervaring mee.
Zoeken op het internet brengt me geen duidelijkheid.
Kan je me iets meer vertellen als ik opgeef dat ik namen invoer in de sheet "Fomulier daguitslag" en gezocht en verwerkt moet worden in de sheet "Uitslagen".

[Lustucru]

Moet je nog iets duidelijker zijn: je voert een naam in op het sheet daguitslag. In welke kolom?
En als die naam niet voorkomt in uitslagen -wederom: welke kolom? - dan moet hij daar aan worden toegevoegd?

[Fasdick]

De naam wordt ingevoerd in kolom B van daguitslag. Gekeken wordt of die naam voorkomt in kolom A van uitslagen. Als die niet voorkomt in kolom A van uitslagen dan moet die in de eerstvolgende lege rij in kolom A worden toegevoegd.
Voor elke week (maandag) over een periode heb ik zo'n formulier daguitslag. Alle uitslagen moeten in de sheer uitslagen komen.
Ik kan je wel de werkmap zenden, zodat je een beter beeld krijgt.

[Lustucru]

Fasdick schreef op dinsdag 26 april 2022 @ 18:29:
[...]
Voor elke week (maandag) over een periode heb ik zo'n formulier daguitslag. Alle uitslagen moeten in de sheer uitslagen komen.

betekent dit ook dat je voor elk periode een apart sheet daguitslag hebt? Zitten er behalve daguitslag (1...n) en uitslagen nog meer sheets in de werkmap?

Ik kan je wel de werkmap zenden, zodat je een beter beeld krijgt.

Nou nee. Ten eerste ga ik geen vreemde bestanden openen en als ik dat wel doe, heb ik graag vooraf een adres waar de factuur naar toe moet.

[Fasdick]

Ja, dat klopt. Ik heb voor elke week een afzonderlijk formulier. Staan wel in één sheet. Namelijk de sheet formulier daguitslag.

Ik begrijp je stelling met betrekking tot het bestand. Zal het niet bijvoegen. Kan wel een afbeelding maken.

[Fasdick]

Ik bedenk me net, dat dat ook een bestand is. Sorry.

[Lustucru]

Dan is het al met al vrij simpel:

Als op het blad daguitslag iets wijzigt dan:
- controleer of de wijziging in kolom B was
- check of de gewijzigde waarde voorkomt in kolom A van het blad uitslagen
- zo niet, schrijf die waarde in de eerste rij onder de laatst gebruikte cel in kolom A

Visual Basic:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newName As String
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        newName = Target.Value
        If Sheets("Uitslagen").Range("A:A").Find(what:=newName) Is Nothing Then
            Sheets("Uitslagen").Range("A65535").End(xlUp).Offset(1, 0).Value = newName
        End If
    End If
End Sub

[ Voor 1% gewijzigd door Lustucru op 27-04-2022 08:12 . Reden: A & B omgedraaid ]

[Fasdick]

De wijziging, d.w.z. de invoering van de naam is in kolom B van daguitslag. Die naam moet in kolom A van uitslagen komen als die er nog niet is. Als die er wel is moeten alleen de punten worden bijgeschreven.

Ik heb de visual basic geheel overgenomen en in de programmacode geplakt.
Invoeren van de naam in kolom B van daguitslag doet niets. Ook niet als ik A:A en B:B verwissel.

Ben morgen overdag afwezig. Geen haast.

[Lustucru]

Aangepast. Je moet de code natuurlijk wel opnemen in het blad daguitslag. Punten bijschrijven kun je of met een formule, of met een kleine aanpassing aan de code.

[dixet]

Is de invoer in blad Daguitslag in één cel, of krijg je daar verschillende rijen met de namen?
In dat laatste geval heb je helemaal geen vba code nodig, maar kan je de UNIQUE functie gebruiken om op het andere tabblad een lijst met unieke namen te krijgen

[Fasdick]

De invoer in blad Daguitslag gaat in verschillende rijen en kolommen met namen. Ik heb in één blad Daguitslag 25 in te vullen daguitslagen staan. Voor elke week één. Ik probeer het uit te leggen.
Links bovenin staat een groep cellen beginnend op (bijvoorbeeld) 25 april 2022. Die groep bestaat (inclusief de koppen) uit cellen van kolom A2 tot en met D16. In kolom A staan van cel 7 t/m 16 de getallen 1 t/m 10. Daarnaast in kolom B zet ik de namen van de 10 spelers die als hoogste zijn geëindigd. Die krijgen punten en die staan in kolom D en lopen van 20 in D7 tot 2 in D16.
Kolom C is een controle kolom die ik nu gebruik om door middel van een aantal handelingen de punten in het blad uitslagen te krijgen.
Daarnaast (naar rechts) staan nog 4 van die groepen (de eerste met datum 2 mei) en daaronder (over de volle breedte) ook 4, zodat ik 25 van die groepen heb.

Het blad Uitslagen is één geheeld. Verticaal komen daarin de namen van spelers die punten hebben behaald; horizontaal komen punten behaald op de dagen waarop de groepen van blad Daguitslagen betrekking hebben. Voor elke dag een kolom.

Bij het invullen van de eerste daguitslag komen onder elkaar de namen in kolom A van het blad Uitslagen beginnend in cel A8 en de behaalde punten in kolom B van dat blad achter de namen.
Bij het invullen van de tweede daguitslag laat ik Excel controleren of de naam al in kolom A van Uitslagen staat. Zo ja, dan wordt in kolom C die uitslag achter die naam vermeld in kolom C. Zo nee, dan wordt de naam van de nieuwe puntenbehaler in kolom A in de eerste lege cel gezet met de punten in kolom C daarachter.
Enzovoorts.

Helemaal rechts in de laatste kolom staat dan het totaal door de speler behaalde punten.

Ik hoop dat dit het duidelijk maakt. Ik heb natuurlijk het beeld, maar ik weet niet of het zo overkomt.

Vind het wel heel wat dat jullie bereid zijn mee te denken. Dank.

[Lustucru]

Voor het basisidee maakt het allemaal niets uit.

Kijk eens naar de regel met intersect(). Die zorgt er nu voor dat de routine alleen wordt uitgevoerd als er iets gewijzigd wordt in kolom 'B'. Die kun je geheel naar wens aanpassen. Bijvoorbeeld:

not (intersect(target;range("B:B") is nothing AND intersect(target;.....) , je kunt controleren of het kolomnummer van target -1 deelbaar is door 4, of dat de waard evan target niet numeriek is etc tec. Daar moet je zelf over nadenken wat de meest handige selectiemanier is. Als je zeker weer dat het enige wat nog in het blad wijzigt de spelersnamen zijn dan zou je heel die if...endif weg kunnen laten.

Fasdick schreef op woensdag 27 april 2022 @ 18:06:
Vind het wel heel wat dat jullie bereid zijn mee te denken. Dank.

Je maakt het ons niet makkelijk nee, met steeds een beetje extra informatie. Het zou helpen als je goed aangeeft wat je niet snapt aan de werking van de code en beter uitlegt hoe je op bepaalde constructies bent gekomen.

[Lustucru]

Stel: je hebt elk blok in een tabel staan. Dan zou je de kolomkop kunnen uitlezen. Of zelfs als het geen tabel is, maar je hebt boven elk blok wel een regeltje met labels staan én je voert de namen keurig van boven naar beneden in dan kun je een constructie bedenken als:

if Target.End(xlUp).Value="Speler" then

[Fasdick]

De manier waarop ik het gemaakt heb is werkend vanuit Excelfuncties en macro's. Ik was zo goed als niet bekend met vba. Vroeger, heel lang geleden, wel Basic gedaan, maar dat is ver weg en bij lange na niet hetzelfde.
Ik heb dus met behulp van functies gecontroleerd of de invoering van een naam een nieuwe naam oplevert en daarbij als dat het geval is de instructie aan mijzelf gegeven, dat ik een macro moet aanroepen. Daarvoor heb ik een besturingselement gemaakt, waarmee ik dat kan doen. Die macro zet de punten achter een bestaande naam of maakt kopieert de nieuwe naam naar Uitslagen en zet daarachter de punten (week voor week).

Intussen heb ik na jullie adviezen gezocht op het internet wat bepaalde instructies betekenen en daarmee verder gewerkt.

Ik ga nu verder met het proberen jullie adviezen in te voeren. Meld mij wel als ik weer ergens op stuit.

[Fasdick]

Beste mensen,
Er is een wonder geschied. Door de range bij Intersect op ("B:V") te zetten werkt het perfect. Ik hoef alleen maar de naam in te vullen en de Uitslagenlijst wordt direct bijgewerkt. Bestaande naam of niet. Het gaat goed.
Dank voor de adviezen.