[Excel] Het grote Excel vragen topic #33

Beste mensen,
ben bezig met wat code in VBA, en natuurlijk gaat er weer iets fout, anders kwam ik hier niet.

Onderstaand de code die ik opgenomen heb in een leeg werkblad om te proberen


Dit is getest, en werkt.
Als ik nu echter dezelfde code toe ga passen in mijn hoofdmap loopt hij er op vast. Code ziet er daar als volgt uit:


Iemand enig idee waarom het verticaal zoeken in een nieuwe lege werkmap wel werkt, maar in de andere code niet?

Alvast bedankt!

quote:

Op woensdag 8 augustus 2012 12:03 schreef Champy het volgende:
Beste mensen,
ben bezig met wat code in VBA, en natuurlijk gaat er weer iets fout, anders kwam ik hier niet.

Onderstaand de code die ik opgenomen heb in een leeg werkblad om te proberen
[ code verwijderd ]

Dit is getest, en werkt.
Als ik nu echter dezelfde code toe ga passen in mijn hoofdmap loopt hij er op vast. Code ziet er daar als volgt uit:
[ code verwijderd ]

Iemand enig idee waarom het verticaal zoeken in een nieuwe lege werkmap wel werkt, maar in de andere code niet?

Alvast bedankt!

omdat je in regel 14 van je code '! hebt staan in plaats van !' ?

[ Bericht 0% gewijzigd door Deetch op 08-08-2012 12:38:14 ]

quote:

Op woensdag 8 augustus 2012 12:29 schreef Deetch het volgende:

[..]

omdat je in regel 14 van je code '! hebt staan in plaats van !' ?

Precies andersom , er moet namelijk '! staan.
Dit gewijzigd, maar hij blijft weer steken op regel 14.

Ergens anders in de hoofdcode, die door iemand anders 2 jaar geleden opgenomen is staat de volgende vertikaal zoeken:


Voordat deze functie gebruikt wordt, wordt ook eerst het betreffende bestand geopend.
Echter als ik deze oude code gebruik om toe te passen op mijn nieuwe vertikaal zoeken werkt deze ook niet

zo te lezen doe je nu een Vlookup in de vierde kolom van een tabel die maar 1 kolom breed is (nl. R1:R65536) waarschijnlijk moet dit een RC achtige verwijzing worden of in ieder geval een breed genoege (hmmmzz) dataset.

hier meer over RC verwijzing.
http://www.lytebyte.com/2(...)ence-style-in-excel/

quote:

Op woensdag 8 augustus 2012 13:16 schreef Deetch het volgende:
zo te lezen doe je nu een Vlookup in de vierde kolom van een tabel die maar 1 kolom breed is (nl. R1:R65536) waarschijnlijk moet dit een RC achtige verwijzing worden of in ieder geval een breed genoege (hmmmzz) dataset.

hier meer over RC verwijzing.
http://www.lytebyte.com/2(...)ence-style-in-excel/

Dat lijkt me sterk, en ik zal uitleggen waarom ik dat denk.
Ten eerste heb ik een macro opname gestart, en daarna de volgende formule ingevuld in de cel:

=ALS.FOUT(VERT.ZOEKEN(B2;'[Producten per leverancier.xlsx]Blad1'!$1:$65536;2;ONWAAR);"")

Deze formule werkt an sich prima.
Omdat VBA hier niet mee om kan gaan maakt ie er zelf de =IFERROR(VLOOKUP code van, dus ik neem aan dat dan alle zoekparameters goed mee overgenomen worden.
Ten tweede werkt de als tweede genoemde "oude" formule met dezelfde RC verwijzing voor alle cellen in het werkblad, R1:R65536, welke dus wel werkt.
Vreemd.

In ieder geval alvast bedankt voor alle input!

quote:

Op woensdag 8 augustus 2012 12:03 schreef Champy het volgende:
Beste mensen,
ben bezig met wat code in VBA, en natuurlijk gaat er weer iets fout, anders kwam ik hier niet.

Onderstaand de code die ik opgenomen heb in een leeg werkblad om te proberen
[ code verwijderd ]

Dit is getest, en werkt.
Als ik nu echter dezelfde code toe ga passen in mijn hoofdmap loopt hij er op vast. Code ziet er daar als volgt uit:
[ code verwijderd ]

Iemand enig idee waarom het verticaal zoeken in een nieuwe lege werkmap wel werkt, maar in de andere code niet?

Alvast bedankt!

Is je 'hoofdmap' een xls file? want in dat geval kan excel in de compatibiliteitsmode staat. Dan kan je niet zoeken naar rijeen boven de 65536 (bestond namelijk nog niet). Dat doe je wel in regel 14. Dus het werkt in principe wel als je er even een xlsx van maakt (in je test bestandje deed hij het wel omdat een nieuwe file standaard een xlsx bestand is).

quote:

Op woensdag 8 augustus 2012 14:37 schreef Cr0sje het volgende:

[..]

Is je 'hoofdmap' een xls file? want in dat geval kan excel in de compatibiliteitsmode staat. Dan kan je niet zoeken naar rijeen boven de 65536 (bestond namelijk nog niet). Dat doe je wel in regel 14. Dus het werkt in principe wel als je er even een xlsx van maakt (in je test bestandje deed hij het wel omdat een nieuwe file standaard een xlsx bestand is).

Bedankt, dat was het probleem! Bestandjes opgeslagen als xlsx en xlsm, verwijzingen in de code ook aangepast en het werkt nu prima! Super bedankt, kan ik weer verder!

Hmmmm misschien een simpele vraag maar ik moet voor elkaar zien te krijgen dat de cellen per week oplopen als je deze naar beneden sleept

dus A1 staat bv op 13 augustus, deze word naar beneden gesleept en dan zou A2 20 augustus moeten worden ipv 14 augustus.

Iemand een idee hoe ik dit voor elkaar kan krijgen?

quote:

Op vrijdag 10 augustus 2012 10:33 schreef qu63 het volgende:

[..]

In A2 -> =A1+7 en dat naar beneden slepen

Mijn dank is groot, zat ik toch een beetje in de buurt

quote:

Op vrijdag 10 augustus 2012 10:34 schreef kidkash19 het volgende:

[..]

Mijn dank is groot, zat ik toch een beetje in de buurt

Je kunt ook in A1 13-9-2012 invoeren en in A2 20-9-2012. Selecteer ze beide en trek ze naar beneden. Op die manier heb je niet al die formules in je worksheet en is je worksheet dus sneller.
Je kunt uiteraard ook de formules gewoon omzetten in vaste waardes.

[ Bericht 92% gewijzigd door DJSeboss op 14-08-2012 19:32:33 ]

Hallo,

Ik ben bezig met de Eredivisie in excel in beeld aan het brengen. Nou heb ik 2 vragen:
Hoe kun je het zo maken (liefst geen VB Code maar als het niet anders kan...) dat als je een uitslag ingeeft dat Excel meteen 3 ptn bij de winnaar toevoegt, het doelsaldo wordt bijgewerkt en er bij W (winst) 1 bijkomt.
Mijn andere vraag is dat het automatisch wordt gesorteerd (nu moet ik de hele tijd Data -> Sorteren doen).
Ik ben niet zo goed met Excel, maar toch wel boven gemiddeld en ik weet niet hoe het heet wat ik nodig heb als iemand me dat geeft ben ik ook tevreden (en helemaal mooi als er dan site bij is waar uitleg bijstaat (voorkeurstaal Nederlands))

Alvast bedankt

Ik heb al gezocht op automatisch sorteren maar dat begreep ik niet zo:
http://www.veit.nl/351258-automatisch-sorteren-mogelijk

En wat betreft data van een sheet naar een ander sheet weet ik niet hoe heet/zoekterm

Sorteren kan ook wel zonder VBA dus ik geef even een andere referentie: [Excel] Het grote Excel vragen topic #31

Even een vraagje:

Ik heb in kolom A de volgende nummers onder elkaar staan: 2, 5, 7, 2 en 8.
Ik heb in kolom B de volgende nummers onder elkaar staan: 1, 2, 4, 5, 6 en 9.

Bestaat er een functie om te kijken hoeveel nummers er uit kolom A in kolom B voorkomen? Antwoord is in dit geval 3 nummers (2, 2 en de 5).

Met de functies '=AANTAL.ALS' en '=AANTALLEN.ALS' kom ik er niet uit.

Het kan wel, maar dan mag je een hele hoop formules aan elkaar plakken;
=AANTAL.ALS(A1:A5;B1)+AANTAL.ALS(A1:A5;B2)+...+AANTAL.ALS(A1:A5;B7)

Weet iemand een betere oplossing?

Volgens mij is het snelste een hulpkolom (C)

in kolom C zet je de functie =ALS(ISFOUT(VERT.ZOEKEN(A1;$B:$B;1;0));0;1)
vervolgens tel je deze kolom op en dat is het aantal getallen in lijst A dat in lijst B voorkomt

quote:

Op donderdag 16 augustus 2012 08:32 schreef Deetch het volgende:
Volgens mij is het snelste een hulpkolom (C)

in kolom C zet je de functie =ALS(ISFOUT(VERT.ZOEKEN(A1;$B:$B;1;0));0;1)
vervolgens tel je deze kolom op en dat is het aantal getallen in lijst A dat in lijst B voorkomt

Bedankt, dat werkt!

Jammer dat Excel geen OF/OR-functie beschikt in dit geval.

=SUM(IF(FREQUENCY(A1:A5;B1:B6)>0;1))

quote:

Op donderdag 16 augustus 2012 11:57 schreef DaFan het volgende:
=SUM(IF(FREQUENCY(A1:A5;B1:B6)>0;1))

Dan worden alleen de unieke waarden geteld. Dus in het voorbeeld komt er dan 2 uit ipv 3 (GuitarJJ telt de 2 nl dubbel).
Het is wel een handige functie trouwens.

Vertaling naar NL: =SOM(ALS(INTERVAL(A1:A5;B:B)>0;1))

Bij mij komt er 3 uit?

quote:

Op donderdag 16 augustus 2012 14:19 schreef DaFan het volgende:
Bij mij komt er 3 uit?

Bij mij ook, maar ook als je cel B2 veranderd in bijvoorbeeld 12 dan blijft de uitkomst op 3 staan

Hoi!

Ik heb een sheet waar ik dingen met een drop-down menu wil kiezen, alleen moeten deze drop downs met elkaar verbonden zijn.

Zo heb ik in het eerste drop-down menu een lijstje met telefoonmerken.


In D:15 kan ik dus de telefoonmerken kiezen. Nu wil ik in E:15 de toestellen van dit merk kunnen kiezen. En daar zit het probleem.

In een ander sheet staat een lijst met alle toestellen, gesorteerd per merk.


Hoe krijg ik die toestellen in het E:15 drop-down menu? Ik zat te denken aan een IF-functie die de van het D:15 gekozen merk de toestellen kopieert naar een aparte celrange. Dan zou ik in principe voor E:15 kunnen refereren naar die nieuwe celrange.

Het probleem zit'm (voor mij) in het feit dat ik met de IF-functie het niet voor elkaar krijg om tekst van meerdere cellen naar lege cellen te kopiëren.

Ik hoop dat het zo duidelijk genoeg is.

quote:

Op donderdag 23 augustus 2012 12:40 schreef qu63 het volgende:

[..]

In dit of het vorige topic is dezelfde vraag ook al langesgekomen, inclusief antwoord

Dan ga ik het even doorspitten.

quote:

Op donderdag 23 augustus 2012 12:40 schreef qu63 het volgende:

[..]

In dit of het vorige topic is dezelfde vraag ook al langesgekomen, inclusief antwoord

Gevonden en (veel te makkelijk ) opgelost. Bedankt voor de heads up.

Ola!

Ik gebruik om het aantal keren te tellen dat een Nee of Ja uit cellen voorkomt het volgende:

Sub Copy_()
MsgBox Evaluate("Sum(COUNTIF(A:L,{""Nee"",""Ja""}))")
End Sub

Dat werkt prima. Alleen nu heb ik grote cellen met lange strings, dus ik wil dat hij door de hele string heen telt. Nu vind het script namelijk alleen 'schone' cellen met los een Nee of een Ja en bijv. niet in cellen waar Ja Nee achter elkaar staan.

Nou heb ik deze code gezien, maar ik kom er niet uit hoe ik precies moet invullen?

MsgBox Evaluate("Sum(COUNTIF(A:L,{""" & Join(v, """,""") & """}))")

Heb natuurlijk al een en ander geprobeerd, maar het lukt vooralsnog niet..

[ Bericht 0% gewijzigd door Stringer op 29-08-2012 22:32:55 ]

-dubbel-

Voor mij is er net te weinig info om de vraag te beantwoorden. Je kan namelijk verschillende dingen willen.

Optie 1: Je wilt het aantal cellen tellen waarin ja of nee in voorkomt (ongeacht of er meer info in staat).
Gebruik wildcards in je huidige code door het toevoegen van *
MsgBox Evaluate("Sum(COUNTIF(A:L,{""*Nee*"",""*Ja*""}))")

Optie 2: Je wilt het aantal cellen tellen de som van het aantal cellen met ja en de som van het aantal cellen met nee. (in dit geval tel je dus een cel met ja nee 2 keer, maar 1 keer wanneer er meerdere keren ja in staat.)

quote:

Dim i As Integer
Dim myCell, rRange As Range
'Aan de hand van deze rRange zorg ik dat je alleen de beschreven cellen in A:L doorloopt
'formules worden niet meegenomen.
Set rRange = ActiveSheet.Range("A:L").SpecialCells(xlCellTypeConstants)

For Each myCell In rRange
'wanneer die ja bevat dan moet je hem meenemen
If myCell Like "*ja*" Then
i = i + 1
End If
'wanneer die nee bevat ook meenemen
If myCell Like "*nee*" Then
i = i + 1
End If
Next myCell
Set rRange = Nothing

maar wellicht nog iets anders?

quote:

Op woensdag 29 augustus 2012 23:10 schreef snabbi het volgende:
Voor mij is er net te weinig info om de vraag te beantwoorden. Je kan namelijk verschillende dingen willen.

Optie 1: Je wilt het aantal cellen tellen waarin ja of nee in voorkomt (ongeacht of er meer info in staat).
Gebruik wildcards in je huidige code door het toevoegen van *
MsgBox Evaluate("Sum(COUNTIF(A:L,{""*Nee*"",""*Ja*""}))")

Optie 2: Je wilt het aantal cellen tellen de som van het aantal cellen met ja en de som van het aantal cellen met nee. (in dit geval tel je dus een cel met ja nee 2 keer, maar 1 keer wanneer er meerdere keren ja in staat.)

[..]

maar wellicht nog iets anders?

Dank voor je antwoord! Ik denk dat ik er zo uit kom.. (anders kom ik nog even terug ;-)

Thanks!!!

[ Bericht 6% gewijzigd door Stringer op 30-08-2012 00:01:54 ]

Let wel op, dat hij het dus maar 1 keer telt wanneer het meerdere keren voorkomt in 1 cel:

quote:

Dim aantalJa, aantalNee, AantalJaNee As Integer
Dim myCell, rRange As Range
Set rRange = ActiveSheet.Range("A:L").SpecialCells(xlCellTypeConstants)

For Each myCell In rRange

If Lcase(myCell.value) Like "*ja*" And Lcase(myCell.value) Like "*nee*" Then
AantalJaNee = AantalJaNee + 1
ElseIf Lcase(myCell.value) Like "*ja*" Then
aantalJa = aantalJa + 1
ElseIf lcase(myCell.value) Like "*nee*" Then
aantalNee = aantalNee + 1
End If
Next myCell
Set rRange = Nothing

*edit: even Lcase toegevoegd zodat alles naar lower case gaat en hij Ja JA en jA ook meetelt.

Mocht je ook binnen een cel meerdere keren willen tellen, dan moeten we daar iets aparts voor schrijven.

[ Bericht 4% gewijzigd door snabbi op 30-08-2012 00:09:19 ]

quote:

Op donderdag 30 augustus 2012 00:00 schreef snabbi het volgende:
Let wel op, dat hij het dus maar 1 keer telt wanneer het meerdere keren voorkomt in 1 cel:

[..]

Mocht je ook binnen een cel meerdere keren willen tellen, dan moeten we daar iets aparts voor schrijven.

ah dank, had even ge-eddited, ben een beetje onduidelijk vanwege het late tijdstip.

Top dankje, morgen verder ;-)

De context is alles bepalend. In mijn voorbeeld wordt een cel met de waarde "ja nee" dus niet meegeteld in het aantal ja's. Dit komt doordat hij eerst kijkt of het en een ja-nee is. Wanneer dit niet zo is, dan test hij nog of het wel een ja bevat.

Dit is ook de reden waarom ik schrijf dat het lastig is je vraag te beantwoorden. Wanneer moet iets wel geteld worden, wanneer niet.

Is het in excel 2007 ook mogelijk om een macro blijvend te maken voor alle documenten.

als ik nu 1 aanmaak blijft hij alleen voor het huidige document?

quote:

Op donderdag 30 augustus 2012 21:34 schreef gerrit098 het volgende:
Is het in excel 2007 ook mogelijk om een macro blijvend te maken voor alle documenten.

als ik nu 1 aanmaak blijft hij alleen voor het huidige document?

Het is mogelijk op de volgende manier:
- Schrijf je code in je Visual Basic editor.
- Sluit de editor en in het normale office document kies je voor Opslaan als
- Kies voor het bestandstype invoegtoepassing (*.xlam)
- Ga nu onder de office knop naar Excel opties
- Kies voor Invoegtoepassingen. Hij staat standaard op Excel invoegtoepassingen. Klik op de knop start.
- Kies nu je bestand.

Voortaan wordt deze invoegtoepassing met jouw specifieke macro ook opgeladen wanneer Excel opent.

*edit
Deze manier werkt alleen voor het aanroepen van een formule in je werkblad. Wanneer je daadwerkelijk subroutines wilt openen moet je naar mijn volgende post gaan.

[ Bericht 7% gewijzigd door snabbi op 31-08-2012 21:45:53 ]

Ik heb het geprobeerd stap voor stap. maar het lukt niet, als ik excel weer open is het qua macro niet terug te vinden

quote:

Op vrijdag 31 augustus 2012 16:47 schreef gerrit098 het volgende:
Ik heb het geprobeerd stap voor stap. maar het lukt niet, als ik excel weer open is het qua macro niet terug te vinden

Ok ik ben inderdaad een beetje snel gegaan. De eerder genoemde methode werkt alleen voor macro's die je hebt geschreven om vanaf je werkboek als formule aan te roepen (de zogenaamde public functions).

Oorzaak probleem
Wellicht had je al gezien dat door het maken van de invoegtoepassing er in je Visual Basic editor een extra project bij is gekomen in je Project editor. Dit extra project zorgt er ook voor dat je vanaf je workbook de functie wel kunt aanroepen. Het hele probleem is eigenlijk gewoon dat je routines in een ander bestand staan en de manier van aanroepen laat alleen het huidige bestand zien.

Voor beide oplossingen moet je eerst mijn vorige post doorlopen hebben!
Oplossing 1: Toevoegen van verwijzing naar je add-in
Deze oplossing is bedoeld om het mogelijk te maken de macro subroutines aan te roepen zoals je dat ook zou doen wanneer het in je huidige code zit.

- Ga in je visual basic editor naar menu Extra
- Verwijzigingen
- Klik op bladeren en voeg het bestand van je invoegtoepassing hier toe.

*Let op in je projectverkenner verschijnt nu een extra menutje met verwijzingen. Op dit moment kun je alle code aanroepen zoals je dat altijd kan, bijvoorbeeld een sub kan je aanroepen via: "Call mijnSub" en functies als x = myFunction(variabele)
** Verwijzingen moet je voor iedere excelfile opnieuw aanmaken.

Oplossing 2: Aanroepen van het andere bestand
Deze oplossing kan je altijd gebruiken zonder iedere keer de add-in toe te voegen en is geschikt voor het aanroepen van de macro codes vanaf je huidige project.

- In de projectverkenner zie je het extra project dat de naam heeft van je invoegtoepassing (bijvoorbeeld VBAproject(test.xlam) ). Klik met je rechter muistoets op dit project en kies voor Eigenschappen van VBAproject.
- Geef het een logische naam (zonder spaties is het makkelijkste, bijvoorbeeld test)
- In je code kun je nu een sub aanroepen via "Call test.Module1.mySub" en een functie als "x = test.Module1.myFunction(variabele)"

Mocht je wel spaties willen gebruiken dan wordt het: "Call [mijn code].Module1.mySub"

Tot slot
Ik weet geen goede manier om de subs in het start lijstje van ALT F8 te krijgen. Eigenlijk is hier de makkelijkste manier om gewoon het betreffende bestand te openen want standaard toont hij alle macro's van de geopende bestanden.

Succes!

[ Bericht 0% gewijzigd door snabbi op 31-08-2012 21:46:47 ]

Ik heb cellen die er als volgt uitzien:

St. Hubertusstraat 10 6531 LB Nijmegen

Dus één adres, postcode + plaats in één cel. Nu wil ik de straatnaam en het huisnummer loskoppelen, maar wat is hiervoor de slimste manier. Ik dacht zelf al via "tekst naar kollomen" (en spatie als nieuwe cel), maar dan moet ik alsnog heel veel werk achteraf gaan doen, aangezien je bij bovenstaande voorbeeld als een spatie na St. hebt. Hoe pak ik dit dus het beste aan?

quote:

Op dinsdag 4 september 2012 11:08 schreef qu63 het volgende:

[..]

Zoeken naar de eerste keer dat er 4 cijfers achter elkaar staan, dan daar voor splitsen. Dan bij het linker gedeelte de laatste spatie wissen, dan splitsen op de laatste spatie. Bij het rechter gedeelte splitsen op de tweede spatie.

Briljant. Thnx

Goedenavond,

Ik heb een vraag over Excel. Met Excel kun je tabellen invoeren met allemaal kolommen aan de bovenkant. Dit dus:

SPOILER

Om spoilers te kunnen lezen moet je zijn ingelogd. Je moet je daarvoor eerst gratis Registreren. Ook kun je spoilers niet lezen als je een ban hebt.

Werkt heel makkelijk en is zeer efficiënt. Alleen is het mij op de een of andere manier gelukt om de kolommen die je bovenaan de tabel ziet te verwijderen. Normaal gesproken kun je deze kolommen gebruiken om dingen te sorteren (je kunt naast kolom 1 op een pijltje klikken en dan aanvinken wat je wilt zien). Maar deze optie zie ik niet meer, ik zie alleen kolom 1 staan zonder de pijl ernaast.

Weet iemand waar dit aan ligt en hoe ik dit kan oplossen?

Alvast Bedankt!

ctrl+shift+l wanneer je in de tabel staat.

quote:

Op dinsdag 4 september 2012 20:05 schreef ralfie het volgende:
ctrl+shift+l wanneer je in de tabel staat.

Ik neem aan dat dit een reactie is op mijn vraag?

Ik heb het geprobeerd, ik zie helaas geen verandering.

quote:

Op dinsdag 4 september 2012 20:17 schreef qu63 het volgende:

[..]

Filter weer aanzetten?

Ik heb het, bedankt.

[ Bericht 4% gewijzigd door iFok. op 04-09-2012 20:25:39 ]

quote:

Op dinsdag 4 september 2012 20:14 schreef iFok. het volgende:

[..]

Ik neem aan dat dit een reactie is op mijn vraag?

Ik heb het geprobeerd, ik zie helaas geen verandering.

Ja. ctr+shift+L zet het filter weer aan (op office 2007 iig, neem aan dat dit niet veranderd in andere versies)