[Excel] Het grote Excel vragen topic #30

zaterdag 28 januari 2012 @ 20:19:17 #1

qu63

..de tijd drinkt..

Welkom bij “Het grote Excel vragentopic” waar je terecht kan met vragen over Excel.

In dit topic is het de bedoeling dat we gericht vragen stellen en antwoorden geven op de gestelde vragen. Om alles zo goed mogelijk te laten verlopen zijn er een aantal regels.

Regels
1. Als je een vraag stelt, zet er dan bij welke versie van Excel je hebt en op welke taal hij ingesteld staat.
2. Zet macrocode tussen [ code ] tags
3. Als het “pseudo-code” is, vermeldt dit dan duidelijk
4. Er bestaan geen domme vragen, wel domme antwoorden. Als jouw antwoord niet gaat bijdragen, geef het antwoord dan niet.
5. Dit topic is geen “code-FEBO” waar je half opgewarmde code uit de muur trekt, het is de bedoeling dat je er zelf iets van leert.

Definities
Functie Een interne functie die bij Excel is geleverd.
UDF Een “user definded function”, een zelfgebouwde functie
Macro Een stuk code die opdrachten uitvoert, gebouwd in (meestal) de VBA editor
Add-In Een Excel workbook met VBA-code (of C#, C) die als .xla (of .xll) is opgeslagen, deze is als vaste plug-in in Excel te plaatsen.
Workbook Het excelbestand
Sheet of Worksheet Het tabblad
Form, Formulier, Userform Een Dialoogvenster in VBA gebouwd

Tips & Links
De help functie van excel of visual basic voor excel kan je een eind op weg helpen. (F1)

Het workbook met het FOK!-menu zoals in de banner weergegeven is hier te vinden. Dit menu is naar wens aan te passen. De icoontjes in het menu zijn hier allemaal weergegeven. In dit workbook zijn drie macro's aanwezig: Een printertool waarmee je uit meerder workbooks sheets kan selecteren voo afdrukken, een power replace die termen kan wijzigen in meerdere workbooks tegelijk en een formule vertaler, de formule in de actieve cell wordt in het engels weergegeven, je kan ook een engelse formule invoeren die dan in de actieve cell in de geldende taal wordt geplaatst. De UDF is naar aanleiding van een vraag in topic #29. De functie kan worden aangeroepen door in een cel =FOK() te typen en dan op de Fx te klikken voor uitleg over de variabelen.

De VBA editor bereik je door ALT+F11 in te drukken. Vanaf Excel 2007 is de VBA editor lastiger te vinden. Op deze site wordt uitgelegd hoe je de editor kan activeren.

De interne functies zijn afhankelijk van welke taal jouw Excel draait. Op deze en deze site kun je de formules in verschillende talen vertalen.

De meer uitgebreide macro's kunnen traag worden, ook kan het scherm flikkeren als gevolg van veel "select" acties. Deels kun je dit ondervangen door zo veel mogelijk select acties te vervangen door directe verwijzingen naar cells en deels door een macro-optimalisatie door te voeren. In de onderstaande code is een routine voor macro-optimalisatie en een routine om de optimalisatie weer uit te zetten weergegeven. In de voorbeeld sheet is een uitgebreide versie van MOA en MOU opgenomen.

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.

Bij macro's die een bepaalde, merkbare, tijd nodig hebben om hun werk te doen is het van belang de gebruiker een idee te geven van wat er gebeurt, zeker met macro-optimalisatie aan kan het lijken alsof Excel is vastgelopen. Je kan bijvoorbeeld de statusbar een waarde geven, met eventueel een percentage van de reeds uitgevoerde code.

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.

Verder
Alle vorige topics zijn hier terug te vinden.

Nieuw topic? Haal de OP uit de wiki

[ Bericht 28% gewijzigd door qu63 op 30-01-2012 15:01:43 ]

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

zaterdag 28 januari 2012 @ 20:21:39 #2

Tegan

quote:
Op vrijdag 27 januari 2012 15:37 schreef qu63 het volgende:

[..]

Ik weet niet of dat ook werkt met je grafieklijn..

Oh, helemaal overheen gelezen

zondag 29 januari 2012 @ 07:56:31 #3

#ANONIEM

Je kan eventueel wel de labels kleuren als je deze weergeeft in de grafiek.

Mijn eerste oplossing werkt trouwens wel als je geen lijn maar een staafdiagram maakt.

op deze site http://peltiertech.com/Excel/ChartsHowTo/ zie je een aantal voorbeelden van grafiek mogelijkheden.

van die site:

[ Bericht 10% gewijzigd door #ANONIEM op 29-01-2012 07:59:24 ]

zondag 29 januari 2012 @ 15:42:05 #4

#ANONIEM

Wazaa

Tja mijn oplossing is uiteindelijk uitgebreide VBA maar goed

Dit is het resultaat:

De sheet is hier te vinden (draai macro PoseNegChart)

en dit is mijn code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

Sub PosNegChart()
    Dim i       As Integer
    Dim j       As Integer
    Dim x       As Range
    Dim y       As Range
    Dim xnew()  As Double
    Dim ynew()  As Double

    'User input x en y waarden
    Set x = Application.InputBox("Kies range X-as", "FOK! Excel Help", , , , , , 8)
    Set y = Application.InputBox("Kies range Y-as", "FOK! Excel Help", , , , , , 8)

    ReDim xnew(0)
    ReDim ynew(0)

    For i = 1 To x.Rows.Count
        'Arrays uitbreiden
        ReDim Preserve xnew(UBound(xnew) + 1)
        ReDim Preserve ynew(UBound(ynew) + 1)
        'x en y waarden aan nieuw array toevoegen
        xnew(UBound(xnew)) = x(i, 1)
        ynew(UBound(ynew)) = y(i, 1)
                'Bekende (x1,y1) en (x2,y2) voor bepaling
                'Richtingscoëfficient en B uit y = ax + b

                A1 = x(i, 1)
                B1 = y(i, 1)

                A2 = x(i + 1, 1)
                B2 = y(i + 1, 1)

                'berekening y = 0 (snijdpunt x-as)
                SP = -1 * (B2 - (A2 * (B2 - B1) / (A2 - A1))) / (B2 - B1) / (A2 - A1)

                    'test of snijden x-as plaatsvindt in deze verzameling
                    If SP > A1 And SP < A2 Then

                        'uitbreiden nieuwe arrays en toevoegen y=0 snijpunten x as
                        ReDim Preserve xnew(UBound(xnew) + 1)
                        ReDim Preserve ynew(UBound(ynew) + 1)
                        ynew(UBound(ynew)) = 0
                        xnew(UBound(xnew)) = SP

                    End If

    Next

    'actieve sheetnaam onthouden
    Dim Naam As String
    Naam = ActiveSheet.Name

    'Geheime sheet aanmaken (eerst verwijderen)
    On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("FOK").Delete
        Application.DisplayAlerts = True
    On Error GoTo 0
        ActiveWorkbook.Sheets.Add
        ActiveSheet.Name = ("FOK")
        Sheets("FOK").Visible = False

    'Nieuwe serie x en y waarden plaatsen in geheime sheet
    For i = 1 To UBound(xnew)
        Sheets("FOK").Cells(i, 1) = xnew(i)
        Sheets("FOK").Cells(i, 2) = ynew(i)
    Next

    'Grafiek toevoegen
    Charts.Add
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = Sheets("FOK").Range(Sheets("FOK").Cells(LBound(xnew) + 1, 1), Sheets("FOK").Cells(UBound(xnew), 1))
    ActiveChart.SeriesCollection(1).Values = Sheets("FOK").Range(Sheets("FOK").Cells(LBound(xnew) + 1, 2), Sheets("FOK").Cells(UBound(xnew), 2))
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.Location Where:=xlLocationAsObject, Name:=Naam

    For i = 1 To UBound(xnew)
        On Error Resume Next
        If ynew(i) <= 0 And ynew(i + 1) <= 0 Then
            ActiveChart.SeriesCollection(1).Points(i + 1).Border.ColorIndex = 3
        End If
    Next


    ActiveChart.Legend.Delete


End Sub

[ Bericht 0% gewijzigd door #ANONIEM op 29-01-2012 16:00:32 ]

zondag 29 januari 2012 @ 18:05:02 #5

ralfie

!Yvan eht nioj

@KritischeMassa

1
2
3
4

Dim x As Variant
x = Range("A1:B4")
MsgBox x(2,2) 'geeft waarde cell B2
Range("A12:B15") = x

Ranges kun je makkelijk converteren naar Variants, en omgekeerd. Gebruik is weinig anders als cells(2 argumenten, rij en kolom), maar snelheid is honderden malen sneller.

zondag 29 januari 2012 @ 18:49:37 #6

#ANONIEM

quote:
Op zondag 29 januari 2012 18:05 schreef ralfie het volgende:
@KritischeMassa
[ code verwijderd ]

Ranges kun je makkelijk converteren naar Variants, en omgekeerd. Gebruik is weinig anders als cells(2 argumenten, rij en kolom), maar snelheid is honderden malen sneller.

ok, dank je, ik zal het meenemen, ik dacht dat ik range moest gebruiken ivm met de input van user, ik had in het begin steeds fouten in de de uitvoering van code en kon niet vinden waar het lag. Uiteindelijk lag het niet aan de ranges maar heel ergens anders...

Ach ja, het is maar zondagmiddag code

maandag 30 januari 2012 @ 09:40:40 #7

Sometimes

Panta Rhei

Ik heb een excel overzicht met daarin in een kolom meetwaarden (getallen) per datum (andere kolom). Ik zou graag willen dat de laagste waarde uit de meetwaardenkolom automatisch een kleurtje krijgt.
Via voorwaardelijke opmaak een functie gehangen aan de kolom:
="min($C$3:$C$28)"
Deze functie heeft echter geen resultaat, geen enkele cel krijgt het gewenste kleurtje. Daarnaast heeft "min" een beperking tot maximaal 30 waarden terwijl ik er veel meer heb staan.

Ik gebruik duidelijk de verkeerde functie, maar welke moet ik dan wel hebben?

Excel 2003.

I removed all the bad food from my house.
It was delicious.

maandag 30 januari 2012 @ 11:07:56 #8

#ANONIEM

Maak een cel waarin je zet =min(c:c)

vervolgens doe je voorwaardelijke opmaak vergelijken met die celwaarde

[ Bericht 1% gewijzigd door #ANONIEM op 30-01-2012 11:08:30 ]

maandag 30 januari 2012 @ 11:24:58 #9

Individual

Meet John Doe...

Mooi, dankjewel KM, ik heb hem in m'n Excelbieb geplakt.

reset

maandag 30 januari 2012 @ 12:55:15 #10

Deetch

WTF?

@Qu63, hoe maak je zo'n extra menu? en kun je die ook installeerbaar maken?

Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten

maandag 30 januari 2012 @ 13:04:49 #11

Basp1

Kan de handige sheet met macros (tip 4) ergens anders geupload worden, de mediafire link werkt niet meer.

maandag 30 januari 2012 @ 13:30:36 #12

#ANONIEM

quote:
Op maandag 30 januari 2012 13:04 schreef Basp1 het volgende:
Kan de handige sheet met macros (tip 4) ergens anders geupload worden, de mediafire link werkt niet meer.

http://www.mediafire.com/?8kbsfc8v0mjyiff

Gek in de OP wiki had ik sowieso ook een ander link geplakt (ik had de OP geheel herschreven)

maandag 30 januari 2012 @ 13:48:01 #13

Sometimes

Panta Rhei

quote:
Op maandag 30 januari 2012 11:07 schreef KritischeMassa het volgende:
Maak een cel waarin je zet =min(c:c)

vervolgens doe je voorwaardelijke opmaak vergelijken met die celwaarde

Bedankt! Werkt perfect.

I removed all the bad food from my house.
It was delicious.

maandag 30 januari 2012 @ 13:53:06 #14

#ANONIEM

quote:
Op maandag 30 januari 2012 12:55 schreef Deetch het volgende:
@Qu63, hoe maak je zo'n extra menu? en kun je die ook installeerbaar maken?

zie de link aan Basp, het menu zit hierin met verklaringen

je kan het helemaal naar je hand zetten

Tip van mij, sla de sheet uiteindelijk op als .xla en zet hem als invoegtoepassing vast

vereist wel een goede verwijzing naar workbooks in je macro's (thisworkbook verwijst dan namelijk naar de .xla bijvoorbeeld)

quote:
Op maandag 30 januari 2012 13:48 schreef Sometimes het volgende:

[..]

Bedankt! Werkt perfect.

geen probleem

[ Bericht 29% gewijzigd door #ANONIEM op 30-01-2012 13:53:40 ]

maandag 30 januari 2012 @ 15:01:12 #15

qu63

..de tijd drinkt..

quote:
Op maandag 30 januari 2012 13:30 schreef KritischeMassa het volgende:

[..]

http://www.mediafire.com/?8kbsfc8v0mjyiff

Gek in de OP wiki had ik sowieso ook een ander link geplakt (ik had de OP geheel herschreven)

Oeps, dan heb ik niet de goeie gepakt.. Ga em ff updaten!

quote:
Op maandag 30 januari 2012 12:55 schreef Deetch het volgende:
@Qu63, hoe maak je zo'n extra menu? en kun je die ook installeerbaar maken?

Geen idee, dat was een screenshot van KritischeMassa

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

maandag 30 januari 2012 @ 15:03:08 #16

qu63

..de tijd drinkt..

OP is geüpdatet, met dank aan KritischeMassa voor het herschrijven en uploaden van het excelbestand!

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

maandag 30 januari 2012 @ 15:05:54 #17

Individual

Meet John Doe...

Maak custom menu's: http://www.ozgrid.com/VBA/custom-menus.htm

reset

maandag 30 januari 2012 @ 15:06:16 #18

Jesse_

Banaan

Wat zitten er eigenlijk voor macro's in het macro bestand?

Het is geel en staat in mijn ondertitel!
3DS friend code: 2191-7623-9035

maandag 30 januari 2012 @ 15:12:26 #19

#ANONIEM

quote:
Op maandag 30 januari 2012 15:06 schreef Jesse_ het volgende:
Wat zitten er eigenlijk voor macro's in het macro bestand?

Zie OP

Een printertool waarmee je sheets uit alle openstaande workbooks kunt selecteren voor uitprinten
Een powerreplace waarmee je een term in alles openstaande workbooks in alle sheets kan vervangen
Een functievertaler, de functie in de actieve cel wordt in het engels weergegeven, ook kun je een functie in het engels ingeven en deze wordt dan in de juiste taal geplaatst

maandag 30 januari 2012 @ 15:14:34 #20

mirelia

Neehee ik ben geen vrouw!!

quote:
Op maandag 30 januari 2012 13:30 schreef KritischeMassa het volgende:

[..]

http://www.mediafire.com/?8kbsfc8v0mjyiff

Gek in de OP wiki had ik sowieso ook een ander link geplakt (ik had de OP geheel herschreven)

NOD32 gaat helemaal over zijn gerrit van deze link

Shuffle up and deal!!!

maandag 30 januari 2012 @ 15:23:29 #21

#ANONIEM

quote:
Op maandag 30 januari 2012 15:14 schreef mirelia het volgende:

[..]

NOD32 gaat helemaal over zijn gerrit van deze link

Dat is gek... er zit niets bijzonders in namelijk

Maar misschien worden VBA macro's per definitie als riskant beoordeeld?

Het is gewoon een zip file met hierin een sheet met macro's

Als je het niet vertrouwd kun je de sheet openen zonder macro's te activeren en de code bekijken

Ik kan leuk programmeren, maar een virus schrijven is een level of 100 te hoog gegrepen voor mij

maandag 30 januari 2012 @ 15:59:26 #22

Jesse_

Banaan

quote:
Op maandag 30 januari 2012 15:23 schreef KritischeMassa het volgende:

[..]

Dat is gek... er zit niets bijzonders in namelijk

Maar misschien worden VBA macro's per definitie als riskant beoordeeld?

Het is gewoon een zip file met hierin een sheet met macro's

Als je het niet vertrouwd kun je de sheet openen zonder macro's te activeren en de code bekijken

Ik kan leuk programmeren, maar een virus schrijven is een level of 100 te hoog gegrepen voor mij

Je kan vast wel een excel bestand koppelen aan een simpel batch filetje dat even een deltree doet oid

Het is geel en staat in mijn ondertitel!
3DS friend code: 2191-7623-9035

maandag 30 januari 2012 @ 16:30:35 #23

DaFan

Je kan een macro een willekeurig bestand laten uitvoeren, dus ja niet echt lastig

Gebruik ik hier ook, maakt bij afsluiten van een master een kopie naar een Alleen-lezen map zodat de andere gebruikers niks kunnen verwijderen.

maandag 30 januari 2012 @ 18:31:45 #24

#ANONIEM

quote:
Op maandag 30 januari 2012 15:59 schreef Jesse_ het volgende:

[..]

Je kan vast wel een excel bestand koppelen aan een simpel batch filetje dat even een deltree doet oid

Ja ok, maar dat batchfiletje moet ik er dan wel bij leveren

en in die zip zit alleen de excel file

Nou goed, de file komt uit een goed hart, als jullie het niet vertrouwen zou ik het niet downloaden, simpel

En geloof je echt dat ik mijn account op het spel zou zetten om iemand zijn harddisk te deleten?

Als iemand die de file wel heeft gedownload en getset heeft wil die hier dan aangeven dat er geen gekke dingen zijn gebeurd?

maandag 30 januari 2012 @ 18:42:08 #25

mirelia

Neehee ik ben geen vrouw!!

Ik kreeg de meldingen op de site, niet op het bestand zelf.
Daar is niets mee aan de hand, excuses voor de verwarring

Shuffle up and deal!!!

maandag 30 januari 2012 @ 18:44:23 #26

#ANONIEM

quote:
Op maandag 30 januari 2012 18:42 schreef mirelia het volgende:
Ik kreeg de meldingen op de site, niet op het bestand zelf.
Daar is niets mee aan de hand, excuses voor de verwarring

aha.. ok, dank je

Heeft iemand een beter alternatief?

Ik heb goede verhalen gehoord over megaupload

dinsdag 31 januari 2012 @ 20:48:26 #27

elkravo

Als je een staaf-/kolomgrafiek hebt. Kun je dan instellen dat de ruimte tussen de staven kleiner wordt, of de staven breder, zodat er minder ruimte tussen zit?

edit: de kolommen/staven tegen elkaar aan zou helemaal mooi zijn...

[ Bericht 6% gewijzigd door elkravo op 31-01-2012 20:55:52 ]

dinsdag 31 januari 2012 @ 21:12:09 #28

ralfie

!Yvan eht nioj

rechtsklikken op je kolommen -> gegevensreeks opmaken->ruimte tussen kolommen naar 0% schuiven

vrijdag 3 februari 2012 @ 17:00:50 #29

Puckzzz

Bitch please!

Ik heb Excel 2010.
Taal geen idee.

Ik heb twee identieke tabbladen met als enig verschil dat ik in tabblad 2 zal werken. (maar de gegevens worden wel overgenomen in tab 1)
Daar komt in een kolom een tijdsnotatie te staan in decimalen. Dus voor 1 uur en een kwartier komt daar te staan 1.25. We ronden alles af naar kwartieren dus doen niet moeilijk met 5 minuutjes e.d.
Nu moet ik dus in diezelfde cel maar dan in tabblad1 dat decimale getal omzetten naar uren en minuten. daar moet dus komen te staan 1:15

Ik krijg het niet voor elkaar

Hey! Het is OK om alleen je eerste twee teennagels te lakken als je van plan bent peeptoes te gaan dragen!

vrijdag 3 februari 2012 @ 17:05:52 #30

DaFan

Hoe kan je de taal niet weten

Het is wel een vreemde notatie die je wilt. 1:15 is in de ogen van Excel kwart over een. Niet 1 uur dubbele punten het aantal minuten. Dat is wel wat lastiger.

vrijdag 3 februari 2012 @ 17:07:08 #31

#ANONIEM

quote:
Op vrijdag 3 februari 2012 17:00 schreef Puckzzz het volgende:
Ik heb Excel 2010.
Taal geen idee.

Ik heb twee identieke tabbladen met als enig verschil dat ik in tabblad 2 zal werken. (maar de gegevens worden wel overgenomen in tab 1)
Daar komt in een kolom een tijdsnotatie te staan in decimalen. Dus voor 1 uur en een kwartier komt daar te staan 1.25. We ronden alles af naar kwartieren dus doen niet moeilijk met 5 minuutjes e.d.
Nu moet ik dus in diezelfde cel maar dan in tabblad1 dat decimale getal omzetten naar uren en minuten. daar moet dus komen te staan 1:15

Ik krijg het niet voor elkaar

=1,25/24

opmaak tijd

[ Bericht 0% gewijzigd door #ANONIEM op 03-02-2012 17:08:14 ]

vrijdag 3 februari 2012 @ 17:10:12 #32

DaFan

Lol ok dat is best makkelijk.
Kritisch owned dit topic

vrijdag 3 februari 2012 @ 17:12:49 #33

#ANONIEM

quote:
Op vrijdag 3 februari 2012 17:10 schreef DaFan het volgende:
Lol ok dat is best makkelijk.
Kritisch owned dit topic

tob al met spreadsheets sinds quatro pro

vrijdag 3 februari 2012 @ 18:29:03 #34

Sjaakbakfiets

Ik heb een vraag over de functie subtotal (data>subtotal).

Om het voorbeeld te vereenvoudigen: dmv subtotal kun je bijvoorbeeld berekenen hoeveel omzet er is gemaakt met de verkoop van laptops (zie http://www.gratiscursus.be/excel_cursus/excelles12c.htm).
Nu wil ik deze informatie niet onder het product komt te staan (in de rij), maar dat er een aparte kolom komt met de totale verkoop. Dus voor elk product laptop, wil ik dat er in een nieuwe kolom telkens komt te staan wat de totale omzet is uit de verkoop van ALLE laptops. Aangezien er in mijn database honderden 'producten' staan, kan dit dus niet elke keer handmatig gelinkt worden.

[Dus stel dat de omzet op het gebied van LAPTOP 1mln is, wil ik in elke rij die over laptops gaan dat er in de nieuwe kolom staat dat de totale omzet 1 mln is, en dit zo bij vele andere producten).

Misschien een beetje moeilijk omschreven maargoed;)
Weet iemand hier het antwoord op?

vrijdag 3 februari 2012 @ 19:37:22 #35

#ANONIEM

Tja...

Ik snap je niet helemaal, maar ik zit te denken aan een UDF (user defined function)

Maar dat kan komen doordat ik je niet helemaal snap en doordat ik vrij snel naar VBA grijp

plaats dit in een module

1
2
3
4
5
6
7
8
9

Function SubT(Bereik As Variant, Term As String, TermKol as integer, OptelKol as integer)

For i = 1 To Bereik.Count
    If Term = Bereik(i, TermKol) Then
        SubT = SubT + Bereik(i, OptelKol)
    End If
Next

End Function

Typ in een cel in excel =subT( en druk op fx

Je kan nu het bereik selecteren, bijvoorbeeld $a$1 tot $b$18 waarin zowel de productnaam als de opbrengst staat

De Term is Laptop (kun je typen, of aanwijzen als bv $a$1)

De TermKol is in dit geval 1

De OptelKol is in dit geval 2

Als je bereik groter is (bv in kolom a staat laptop en in kolom e staat het bedrag dan is de optelkol 5, ze zijn altijd relatief, dus als het bereik van d naar e loopt is het weer kolom 1 en 2)

Wat de functie doet is in het bereik kijken of de term in de term kolom staat en zo ja telt hij het getal dat in de optelkolom staat bij SubT op

Hier een sheet met de functie erin

http://www.mediafire.com/?ctuzcusvyd2623u

[ Bericht 5% gewijzigd door #ANONIEM op 03-02-2012 19:43:05 ]

vrijdag 3 februari 2012 @ 19:55:45 #36

#ANONIEM

Een ander idee is dat je een extra tabel maakt met hierin de totalen voor elk producttype:

laptops 1.000.000
PC 500.000
komkommers 3.000.000

en dan in de kolom die je wil maken via vertikaal zoeken de juiste subtotalen erbij zoeken

zie voorbeeld:

http://www.mediafire.com/?srq485z9jm4e1t9

[ Bericht 11% gewijzigd door #ANONIEM op 03-02-2012 19:59:29 ]

vrijdag 3 februari 2012 @ 20:07:31 #37

Sjaakbakfiets

Dank voor je uitgebreide reactie, ik ga er mee aan de slag!

vrijdag 3 februari 2012 @ 20:14:08 #38

#ANONIEM

quote:
Op vrijdag 3 februari 2012 20:07 schreef Sjaakbakfiets het volgende:
Dank voor je uitgebreide reactie, ik ga er mee aan de slag!

Mijn tweede suggestie is beter en sneller (UDF is traag)

edit:

die hele tussentabel is helemaal niet nodig

=SOM.ALS($A$1:$B$19;A1;$B$1:$B$19)

[ Bericht 14% gewijzigd door #ANONIEM op 03-02-2012 20:18:16 ]

vrijdag 3 februari 2012 @ 20:27:22 #39

Sjaakbakfiets

Ik was inderdaad met de 2e bezig

Ik laat je weten als het gelukt is!

Edit: gelukt! Een hele dag aangeplaard, en nu binnen 5 minuten geregeld. Wat kan excel toch makkelijk zijn als je weet hoe het werkt. Nogmaals bedankt!:D

[ Bericht 50% gewijzigd door Sjaakbakfiets op 03-02-2012 20:37:22 ]

vrijdag 3 februari 2012 @ 20:38:01 #40

Sjaakbakfiets

Mocht je een oplossing hebben voor mijn volgend probleem dan hoor ik het graag

Ik heb 2 excelbestanden (respectievelijk 4000 en 8000 rijen), en in beide bestanden zit een kolom met codes. In totaal zijn ongeveer 3500 van deze codes hetzelfde. U raadt het al, deze 3500 codes (met bijbehorende informatie) moet ik filteren.
Weet jij/iemand hoe ik deze bestanden kan matchen?

Edit: grotendeels gelukt, dmv formule ALS(ISFOUT(VERT.ZOEKEN(A1;B:B;1;0));"";VERT.ZOEKEN(A1;B:B;1;0))

[ Bericht 10% gewijzigd door Sjaakbakfiets op 04-02-2012 00:45:47 ]

zaterdag 4 februari 2012 @ 23:09:33 #41

McGilles

quote:
Op vrijdag 3 februari 2012 20:38 schreef Sjaakbakfiets het volgende:
Mocht je een oplossing hebben voor mijn volgend probleem dan hoor ik het graag

Ik heb 2 excelbestanden (respectievelijk 4000 en 8000 rijen), en in beide bestanden zit een kolom met codes. In totaal zijn ongeveer 3500 van deze codes hetzelfde. U raadt het al, deze 3500 codes (met bijbehorende informatie) moet ik filteren.
Weet jij/iemand hoe ik deze bestanden kan matchen?

Edit: grotendeels gelukt, dmv formule ALS(ISFOUT(VERT.ZOEKEN(A1;B:B;1;0));"";VERT.ZOEKEN(A1;B:B;1;0))

Waarom niet helemaal dan?

zondag 5 februari 2012 @ 15:24:54 #42

elkravo

Stel ik heb een vrij lange Excel lijst/tabel. Hierin staat een kolom met categoriën, bijvoorbeeld A, B, C.
Iedere rij/object heeft dus een categorie A B of C gekregen, hierin zit geen volgorde

Nu wil ik graag dat er ondere iedere rij van categorie B een extra lege regel wordt toegevoegd.

Beperking: ik kan/mag de lijst NIET sorteren omdat ik dan nooit meer terugsorteren kan naar de huidige volgorde.

Kan dit makkelijk, zonder dat ik handmatig rijen 1-voor-1 moet toevoegen?

zondag 5 februari 2012 @ 15:27:40 #43

qu63

..de tijd drinkt..

quote:
Op zondag 5 februari 2012 15:24 schreef elkravo het volgende:
Stel ik heb een vrij lange Excel lijst/tabel. Hierin staat een kolom met categoriën, bijvoorbeeld A, B, C.
Iedere rij/object heeft dus een categorie A B of C gekregen, hierin zit geen volgorde

Nu wil ik graag dat er ondere iedere rij van categorie B een extra lege regel wordt toegevoegd.

Beperking: ik kan/mag de lijst NIET sorteren omdat ik dan nooit meer terugsorteren kan naar de huidige volgorde.

Kan dit makkelijk, zonder dat ik handmatig rijen 1-voor-1 moet toevoegen?

Macro/VBA

KritischeMassa zal zo wel langskomen om het uit te leggen

_{Of zoek uit hoe je t zelf kan doen, ik kan t namelijk ook (nog) niet goed (genoeg)}

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

zondag 5 februari 2012 @ 15:30:46 #44

elkravo

quote:
Op zondag 5 februari 2012 15:27 schreef qu63 het volgende:

[..]

Macro/VBA

KritischeMassa zal zo wel langskomen om het uit te leggen
_{Of zoek uit hoe je t zelf kan doen, ik kan t namelijk ook (nog) niet goed (genoeg)}

Ik probeer zoveel mogelijk macro's VBA te vermijden omdat ik er geen verstand van heb. Liefst een simpel trucje. Het betreft een eenmalige verbouwing van een excel lijst waarbij ik onder bepaalde rijen van een categorie een extra regel moet toevoegen. En de lijst is 10.000 rijen...

zondag 5 februari 2012 @ 15:47:59 #45

#ANONIEM

quote:
Op zondag 5 februari 2012 15:30 schreef elkravo het volgende:

[..]

Ik probeer zoveel mogelijk macro's VBA te vermijden omdat ik er geen verstand van heb. Liefst een simpel trucje. Het betreft een eenmalige verbouwing van een excel lijst waarbij ik onder bepaalde rijen van een categorie een extra regel moet toevoegen. En de lijst is 10.000 rijen...

Ja ik zou wel VBA gebruiken

Maar ja

Het invoegen van regels is nu eenmaal niet mogelijk met een formule, of lastig.

Als je in VBA gaat klooien maak dan wel eerst een backup van de huidige situatie.

1
2
3
4
5
6
7
8
9
10
11
12

Sub InsertLines()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For i = LastRow To 1 Step -1
        If ActiveSheet.Cells(i, 1) = "B" Then
            Rows(i + 1).Insert Shift:=xlDown
        End If
    Next
End Sub

Deze code in de betreffende sheet plakken (in het VBA scherm) en de macro draaien

Deze code gaat ervan uit dat de ABC in kolom A staat (activesheet.cells(i,1) = "B") de 1 in cells verwijzing slaat op kolom A dat kun je aanpassen

[ Bericht 3% gewijzigd door #ANONIEM op 05-02-2012 15:49:48 ]

zondag 5 februari 2012 @ 16:00:21 #46

#ANONIEM

quote:
Op zondag 5 februari 2012 15:24 schreef elkravo het volgende:

Beperking: ik kan/mag de lijst NIET sorteren omdat ik dan nooit meer terugsorteren kan naar de huidige volgorde.

Je kan altijd terug sorteren als je eerst een kolom maakt die gewoon telt van 1 tot einde

zondag 5 februari 2012 @ 16:17:54 #47

elkravo

quote:
Op zondag 5 februari 2012 15:47 schreef KritischeMassa het volgende:

[..]

Ja ik zou wel VBA gebruiken

Maar ja

Het invoegen van regels is nu eenmaal niet mogelijk met een formule, of lastig.

Als je in VBA gaat klooien maak dan wel eerst een backup van de huidige situatie.
[ code verwijderd ]

Deze code in de betreffende sheet plakken (in het VBA scherm) en de macro draaien

Deze code gaat ervan uit dat de ABC in kolom A staat (activesheet.cells(i,1) = "B") de 1 in cells verwijzing slaat op kolom A dat kun je aanpassen

1 vraagje... in de code, waar staat de A voor in "LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ??

zondag 5 februari 2012 @ 16:20:20 #48

#ANONIEM

quote:
Op zondag 5 februari 2012 16:17 schreef elkravo het volgende:

[..]

1 vraagje... in de code, waar staat de A voor in "LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ??

Hij kijkt in kolom A wat de laatste regel is

Ik ben uitgegaan van kolom A

Je kan ook de lastrow opgeven als je die weet (bijvoorbeeld rij 32012)

Dan kan je volstaan met

1
2
3
4
5
6
7

Sub InsertLines()
    For i = 32012 To 1 Step -1
    If ActiveSheet.Cells(i, 1) = "B" Then
        Rows(i + 1).Insert Shift:=xlDown
    End If
    Next
End Sub

zondag 5 februari 2012 @ 16:26:31 #49

elkravo

quote:
Op zondag 5 februari 2012 16:20 schreef KritischeMassa het volgende:

[..]

Hij kijkt in kolom A wat de laatste regel is

Ik ben uitgegaan van kolom A

Je kan ook de lastrow opgeven als je die weet (bijvoorbeeld rij 32012)

Dan kan je volstaan met
[ code verwijderd ]

Ok... dan snap ik 'm... ben geen VBA held ;-)... normaal als ik een rij toevoeg, duurt dat 3 seconden ivm formuleberekeningen... de macro runt nu... zal wel even duren ;-)... laat m'n comp gewoonjakkeren... Hopelijk kom ik straks terug met goed het resultaat op m'n scherm.

zondag 5 februari 2012 @ 16:28:05 #50

#ANONIEM

quote:
Op zondag 5 februari 2012 16:26 schreef elkravo het volgende:

[..]

Ok... dan snap ik 'm... ben geen VBA held ;-)... normaal als ik een rij toevoeg, duurt dat 3 seconden ivm formuleberekeningen... de macro runt nu... zal wel even duren ;-)... laat m'n comp gewoonjakkeren... Hopelijk kom ik straks terug met goed het resultaat op m'n scherm.

Ach had dat gezegd

dan had ik wat regels code toegevoegd om de berekeningen uit te zetten...

Je hebt wel een back up gemaakt eerst?

zondag 5 februari 2012 @ 16:39:07 #51

elkravo

quote:
Op zondag 5 februari 2012 16:28 schreef KritischeMassa het volgende:

[..]

Ach had dat gezegd

dan had ik wat regels code toegevoegd om de berekeningen uit te zetten...

Je hebt wel een back up gemaakt eerst?

Yep... dat doe ik sowieso... heb je die extra code... want heb het gevoel dat mijn comp het niet aankan... ;-)

Excel lijkt vast te lopen.... maar kan niet zien of regels toegevoegd worden dus... hij begint niet bovenaan lijkt het sowieso

zondag 5 februari 2012 @ 16:43:43 #52

#ANONIEM

Nee hij begint onderaan

ok

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

Sub InsertLines()
MOA
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For i = LastRow To 1 Step -1
    If ActiveSheet.Cells(i, 1) = "B" Then
        Rows(i + 1).Insert Shift:=xlDown

    End If


    Next

MOU
End Sub

Sub MOA()
'MacroOptimalisatieAan
On Error Resume Next
    Application.Cursor = xlWait
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
End Sub

Sub MOU()
'MacroOptimalisatieUit
On Error Resume Next
    Application.Cursor = xlDefault

    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
End Sub

[ Bericht 4% gewijzigd door #ANONIEM op 05-02-2012 16:44:16 ]

zondag 5 februari 2012 @ 17:03:57 #53

elkravo

quote:
Op zondag 5 februari 2012 16:43 schreef KritischeMassa het volgende:
Nee hij begint onderaan

ok
[ code verwijderd ]

Dat werkte een stuk sneller... koste 5 minuutjes nu ;-)

VEEL DANK!

enne... ik kan die MOA en MOU nu dus ook gebruiken als ik andere veranderingen wil doen die steeds geremd worden door herberekenen?

zondag 5 februari 2012 @ 17:05:08 #54

#ANONIEM

quote:
Op zondag 5 februari 2012 17:03 schreef elkravo het volgende:

[..]

Dat werkte een stuk sneller... koste 5 minuutjes nu ;-)

VEEL DANK!

mooi

zondag 5 februari 2012 @ 17:14:23 #55

Sjaakbakfiets

quote:
Op zaterdag 4 februari 2012 23:09 schreef McGilles het volgende:

[..]

Waarom niet helemaal dan?

Mijn oorspronkelijke vraag is wel volledig opgelost, maar het totale probleem nog niet helemaal

zondag 5 februari 2012 @ 17:20:18 #56

#ANONIEM

quote:
Op zondag 5 februari 2012 17:14 schreef Sjaakbakfiets het volgende:

[..]

Mijn oorspronkelijke vraag is wel volledig opgelost, maar het totale probleem nog niet helemaal

Die opmerking sloeg op jouw edit, je gaf aan dat het was gelukt maar niet helemaal. Wat lukt er dan niet?

Zonder een duidelijke probleemomschrijving is het moeilijk oplossen, ik ben geen cup-a-soup

zondag 5 februari 2012 @ 19:00:47 #57

Corporaal

Hoi,

Ik ben bezig met een spreadsheet voor de status van verschillende projecten. Om het geheel overzichtelijk te houden heb ik aan de status van de diverse projecten kleuren gekoppeld. Nu wil ik graag een losse legenda.Eigenlijk een soort pop up.

Kan dit in Excel?

zondag 5 februari 2012 @ 19:17:59 #58

#ANONIEM

Ja, hebbie KM weer met zijn VBA maar daar zou ik het in doen

Je kan gewoon een userform aanmaken met labels en die kleuren

Je kan dan of een menu maken zoals in de OP is weergegeven of de legenda aan het rechtermuisklikmenu toevoegen

Of achter een knop of of of

Open je vba editor en voeg een userform in

Sleep er wat labels heen en in de linkeronderhoek van je scherm kun je de eigenschappen instellen

Dus caption is bijvoorbeeld Project Komkommer, backcolor = groen

Sleep er ook een knop in en dubbelklik op die knop om de code "unload me" in te geven

Noem de knop (caption) OK of Sluit oid

edit:

Ik zou de sheet downloaden uit de OP en deze aanvullen met jouw userform (en de extra menu items verwijderen)

In deze sheet zitten al wat userforms dus dan heb je wat voorbeelden, en ook wat code her en der

[ Bericht 15% gewijzigd door #ANONIEM op 05-02-2012 19:20:46 ]

zondag 5 februari 2012 @ 22:01:03 #59

qu63

..de tijd drinkt..

quote:
Op zondag 5 februari 2012 19:00 schreef Corporaal het volgende:
Hoi,

Ik ben bezig met een spreadsheet voor de status van verschillende projecten. Om het geheel overzichtelijk te houden heb ik aan de status van de diverse projecten kleuren gekoppeld. Nu wil ik graag een losse legenda.Eigenlijk een soort pop up.

Kan dit in Excel?

Is een Gantt-chart geen optie?

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

zondag 5 februari 2012 @ 22:01:23 #60

qu63

..de tijd drinkt..

quote:
Op zondag 5 februari 2012 16:43 schreef KritischeMassa het volgende:
Nee hij begint onderaan

ok
[ code verwijderd ]

MOA en MOUA zijn handig! Thankks!

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

maandag 6 februari 2012 @ 06:21:52 #61

#ANONIEM

Zorg wel dat je altijd MOU draait dus ook na fout afhandeling in macro code

Anders doet je excel het niet meer

(na herstart van excel wel trouwens hoor, maar omdat screenupdate uitstaat lijkt excel stuk)

In de application.statusbar kun je communiceren met de gebruiker, bijvoorbeeld percentage klaar weergeven, of gewoon de waarde van teller i bijvoorbeeld. Zo heeft de gebruiker ook het idee dat er wat gebeurt.

Edit:

Als jouw macro afhankelijk is van tussentijdse celuitkomsten moet je wel eerst de moa draaien en dan weer mou anders staat er niets in de cel want de berekeningen staan uit

[ Bericht 13% gewijzigd door #ANONIEM op 06-02-2012 06:51:52 ]

maandag 6 februari 2012 @ 10:35:14 #62

Deetch

WTF?

mooi verhaal KM, deze ga ik veel gebruiken. Nog wel even puzzelen hoe die statusbar werkt.

Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten

maandag 6 februari 2012 @ 12:09:16 #63

qu63

..de tijd drinkt..

quote:
Op maandag 6 februari 2012 06:21 schreef KritischeMassa het volgende:
Zorg wel dat je altijd MOU draait dus ook na fout afhandeling in macro code

Anders doet je excel het niet meer (na herstart van excel wel trouwens hoor, maar omdat screenupdate uitstaat lijkt excel stuk)

In de application.statusbar kun je communiceren met de gebruiker, bijvoorbeeld percentage klaar weergeven, of gewoon de waarde van teller i bijvoorbeeld. Zo heeft de gebruiker ook het idee dat er wat gebeurt.

Edit:

Als jouw macro afhankelijk is van tussentijdse celuitkomsten moet je wel eerst de moa draaien en dan weer mou anders staat er niets in de cel want de berekeningen staan uit

Jouw versie is net wat uitgebreider dan wat ik zelf al had, die stopte en startte alleen het herberekenen namelijk..

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

maandag 6 februari 2012 @ 17:17:03 #64

#ANONIEM

quote:
Op maandag 6 februari 2012 10:35 schreef Deetch het volgende:
mooi verhaal KM, deze ga ik veel gebruiken. Nog wel even puzzelen hoe die statusbar werkt.

1
2
3
4
5
6
7
8
9
10
11

Sub StatBarDemo()

'Een simpel tellertje in de statusbar weergeven
For i = 0 To 32000
Application.StatusBar = "Percentage uitgevoerd: " & CInt((i / 32000) * 100) & "%"
Next

'statusbar weer resetten
Application.StatusBar = False

End Sub

De progressbar is niet zo 1 2 3 te benaderen, maar daar kun je op deze manier omheen

1
2
3
4
5
6
7
8
9

Sub StatBarDemo()
'Een soort van progress bar in de statusbar weergeven
For i = 0 To 32000
Application.StatusBar = (CInt((i / 32000) * 100)) & "% " & String(CInt((i / 32000) * 10), ChrW(9609))
Next
'statusbar weer resetten
Application.StatusBar = False

End Sub

Als ik overigens hele uitgebreide code draai, zeg maar macro's van rond de 5 mb aan code dan speel ik vals en maak ik een optelsom van het percentage

Ik schat dan in hoe lang elke deel procedure ongeveer duurt en dan tel ik dat op bij het percentage.

Het gaat er niet zo om of het percentage echt klopt (zie ook windows progressbars, die kloppen ook nooit van een meter, 90% in een seconde en dan over de laatste 10% een half uur doen

)

Het gaat er om dat je de gebruiker het gevoel geeft dat er iets gebeurt, mensen zijn snel geneigd om op esc te drukken of op CTRL + ALT + Delete, met de mededeling "Hij liep vast

" Terwijl het gewoon soms tijd kost om iets te laten uitvoeren.

Zeker als je iets bouwt voor een groep gebruikers is het van belang ze het gevoel te geven dat er iets gebeurt, als het voor jezelf is, is het vooral opleuken... Ook leuk natuurlijk

Ik bouw zelf macro's voor een middelgroot ingenieursbureau, vrij uitgebreide scripts die samenwerken met een aantal bussinessystemen en datacollectie systemen (loggerdata, veldwerkdata etc), die een geweldig kloterige uitvoer hebben, maar die zijn door een hotemetoot aangekocht die inmiddels met een dikke handdruk elders werkt...

Uit irritatie hierover ben ik ooit zelf begonnen met het maken van die dingen, zo erg dat ik mijn eigen werkzaamheden zowat had weggeprogrammeerd. Tegenwoordig mag ik een deel van mijn tijd besteden aan dit soort crea-bea gehobby

[ Bericht 14% gewijzigd door #ANONIEM op 06-02-2012 18:51:56 ]

maandag 6 februari 2012 @ 19:28:01 #65

elkravo

Hoi KM,

Dit moet een eitje zijn voor je:

In een lijst van 10.000 items (lengte beperking), staan in kolom B datums.
Willekeurig ontbreken op bepaalde regels/rijen datums (lege cel).

Er is nooit sprake van twee lege regels/rijen.

Is er een leuk VBAtje waarbij van cel B5 t/m B10.005 wordt doorlopen en indien lege cel, de datum(-waarde (paste value)) van de bovenstaande wordt gekopieerd en geplakt? Zou me handmatig een-voor-een copy-pasten schelen.

Bij voorbaat dank!

maandag 6 februari 2012 @ 19:41:50 #66

#ANONIEM

quote:
Op maandag 6 februari 2012 19:28 schreef elkravo het volgende:
Hoi KM,

Dit moet een eitje zijn voor je:

In een lijst van 10.000 items (lengte beperking), staan in kolom B datums.
Willekeurig ontbreken op bepaalde regels/rijen datums (lege cel).

Er is nooit sprake van twee lege regels/rijen.

Is er een leuk VBAtje waarbij van cel B5 t/m B10.005 wordt doorlopen en indien lege cel, de datum(-waarde (paste value)) van de bovenstaande wordt gekopieerd en geplakt? Zou me handmatig een-voor-een copy-pasten schelen.

Bij voorbaat dank!

Dat zou je zelf kunnen uitvinden aan de hand van reeds gepostte code, maar ok, ik ben de kwaadste niet

Deze code in het werkblad of een module plakken

1
2
3
4
5
6
7
8
9
10
11

Sub AddDate()
MOA 'sub MOA is een paar posts terug te vinden

    For i = 5 To 10005
        If ActiveSheet.Cells(i, 2) = "" Then
            ActiveSheet.Cells(i, 2) = ActiveSheet.Cells(i - 1, 2)
        End If
    Next

MOU 'sub MOU is een paar posts terug te vinden
End Sub

[ Bericht 3% gewijzigd door #ANONIEM op 06-02-2012 19:42:55 ]

maandag 6 februari 2012 @ 19:56:44 #67

elkravo

quote:
Op maandag 6 februari 2012 19:41 schreef KritischeMassa het volgende:

[..]

Dat zou je zelf kunnen uitvinden aan de hand van reeds gepostte code, maar ok, ik ben de kwaadste niet

Deze code in het werkblad of een module plakken
[ code verwijderd ]

Nee je bent de kwaadste niet...

Ik kan VBA wel beredeneren als ik het zie, maar bedenken wordt toch heel wat moeilijker... harstikke bedankt!

maandag 6 februari 2012 @ 20:51:28 #68

qu63

..de tijd drinkt..

quote:
Op maandag 6 februari 2012 17:17 schreef KritischeMassa het volgende:
Uit irritatie hierover ben ik ooit zelf begonnen met het maken van die dingen, zo erg dat ik mijn eigen werkzaamheden zowat had weggeprogrammeerd. Tegenwoordig mag ik een deel van mijn tijd besteden aan dit soort crea-bea gehobby

Zoeken jullie nog collega's?

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

dinsdag 7 februari 2012 @ 06:37:22 #69

#ANONIEM

quote:
Op maandag 6 februari 2012 20:51 schreef qu63 het volgende:

[..]

Zoeken jullie nog collega's?

Goeie meettechnici zijn schaars, dus ja die zeker

dinsdag 7 februari 2012 @ 08:35:01 #70

Frixel

Hallo,

Bestaat er een optie in Excel of een Macro dat er twee bestanden met elkaar vergeleken worden en dat er dan één bestand (bv masterbestand)
word aangevuld met de gegevens die in het 2e bestand meer staan?
Eigenlijk is het dus een soort synchronisatie?

Ik wil dus eigenlijk de kolommen A tm H vergelijken.

dinsdag 7 februari 2012 @ 08:38:40 #71

#ANONIEM

Het antwoord van Wokkel in jouw eerste topic voldeed niet?

Stel dat A1 blad 1 gelijk is aan A23 blad 2 moet dan ook B1 met B23 worden vergeleken? en zo verder voor C t/m H of is een vergelijking van A met A voldoende?

dinsdag 7 februari 2012 @ 08:52:17 #72

Frixel

quote:
Op dinsdag 7 februari 2012 08:38 schreef KritischeMassa het volgende:
Het antwoord van Wokkel in jouw eerste topic voldeed niet?

Stel dat A1 blad 1 gelijk is aan A23 blad 2 moet dan ook B1 met B23 worden vergeleken? en zo verder voor C t/m H of is een vergelijking van A met A voldoende?

Inderdaad, als A1 blad 1 gelijk is aan A23 blad 2 dan moet ook B1 met B23 worden vergeleken en zo verder want er kan een "afwijking" in de volgende kolommen voorkomen.
dat maakt het zo ingewikkeld vrees ik.

dinsdag 7 februari 2012 @ 08:56:33 #73

#ANONIEM

Niet ingewikkeld, wel traag (indien VBA)

Ik zou eerst eens de tip van wokkel uitproberen.

Anders kom ik hier vanmiddag/avond op terug (zit nu op mijn werk

)

dinsdag 7 februari 2012 @ 09:46:30 #74

qu63

..de tijd drinkt..

quote:
Op dinsdag 7 februari 2012 06:37 schreef KritischeMassa het volgende:

[..]

Goeie meettechnici zijn schaars, dus ja die zeker

Hmm, dan zit ik met mijn opleiding Small Business & Retail Management niet echt goed geloof ik

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

dinsdag 7 februari 2012 @ 11:31:45 #75

Setting_Sun

Excel 2010 de look van Excel 2007 geven, kan dat?

dinsdag 7 februari 2012 @ 11:50:33 #76

DaFan

quote:
Op dinsdag 7 februari 2012 11:31 schreef Setting_Sun het volgende:
Excel 2010 de look van Excel 2007 geven, kan dat?

Nee. Wen er maar aan

En ook al zou het kunnen zou ik het niet doen als ik jou was.

dinsdag 7 februari 2012 @ 12:51:36 #77

Deetch

WTF?

quote:
Op dinsdag 7 februari 2012 11:31 schreef Setting_Sun het volgende:
Excel 2010 de look van Excel 2007 geven, kan dat?

gewoon even eraan wennen.

Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten

dinsdag 7 februari 2012 @ 17:14:11 #78

#ANONIEM

quote:
Op dinsdag 7 februari 2012 08:52 schreef Frixel het volgende:

[..]

Inderdaad, als A1 blad 1 gelijk is aan A23 blad 2 dan moet ook B1 met B23 worden vergeleken en zo verder want er kan een "afwijking" in de volgende kolommen voorkomen.
dat maakt het zo ingewikkeld vrees ik.

Werkte de tip van Wokkel?

dinsdag 7 februari 2012 @ 20:10:37 #79

#ANONIEM

Op deze locatie kun je een zipfile downloaden die de volgende files bevat:

Master (voorbeeld file)
Slave (voorbeeld file)

MergeFile.xls (excel sheet)
MergeFile.xla (add-in)

Mergefile gaat het om, de master en slave zijn twee files die samengevoegd moeten worden

Ik ben ervan uitgegaan dat in zowel de master als de slavefile het de eerste sheet (tabblad) betreft

Er zit een hoop bla bla code bij om het één en ander gebruiksvriendelijk te maken (een menu met een item, een userform voor het aanwijzen van de files en MOU en MOA)

Uiteindelijk gaat het om de routine MergeFiles. (zie code hieronder). Wat je moet weten is dat "sheets(1)" slaat op het eerste tabblad, je kan dit zelf (consequent!) aanpassen (WB1 is de master, wb2 is de slave)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

Sub MergeFiles()
    If WB_1 = "" Then Exit Sub
    If WB_2 = "" Then Exit Sub
    MOA

        Workbooks.Open (WB_1)
        WB_1_Name = ActiveWorkbook.Name
        WB_1_LastCell = Workbooks(WB_1_Name).Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
        Workbooks.Open (WB_2)
        WB_2_Name = ActiveWorkbook.Name
        WB_2_LastCell = Workbooks(WB_2_Name).Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
        NewRow = WB_1_LastCell + 1

        'regel i uit de slave file moet vergeleken worden met iedere regel j uit de masterfile
        'als regel i voorkomt in de masterfile dan doet de routine niets
        'als regel i niet voorkomt in de masterfile zet de routine regel i onder de masterfile


        For i = 1 To WB_2_LastCell
        Application.StatusBar = CInt(i / WB_2_LastCell * 100) & "% klaar"
        SlaveStr = ""
        MasterStr = ""
        UniqueRow = True
            For KolTel = 1 To 8
                SlaveStr = SlaveStr & Workbooks(WB_2_Name).Sheets(1).Cells(i, KolTel)
            Next
                For j = 1 To WB_1_LastCell
                    MasterStr = ""
                    For KolTel = 1 To 8
                        MasterStr = MasterStr & Workbooks(WB_1_Name).Sheets(1).Cells(j, KolTel)
                    Next

                    If SlaveStr = MasterStr Then
                        UniqueRow = False
                    End If
                Next

            If UniqueRow = True Then
                For KolTel = 1 To 8
                    Workbooks(WB_1_Name).Sheets(1).Cells(NewRow, KolTel) = Workbooks(WB_2_Name).Sheets(1).Cells(i, KolTel)
                Next
            NewRow = NewRow + 1
            End If
        Next

        Workbooks(WB_2_Name).Close False
    MOU
End Sub

MergeFile is twee keer toegevoegd, 1 keer als xls (een gewoon werkboek) en 1 keer als .xla. Dit is een add-in die je via invoegtoepassingen standaard in excel kunt installeren

Ik heb klachten gezien dat je virusscanner op mediafire kan afgaan, daar kan ik niets aan doen. De code zelf in de xla en de xls is identiek en vrij toegankelijk en is virus vrij.

Ik raad je wel aan om altijd een backup te maken van je master en slave file voor je gaat testen met mijn code, want tja, ik blijf een hobby programmeur.

Als je Mediafire niet vertrouwt kan ik je de zipfile ook mailen, DM me dan.

[ Bericht 46% gewijzigd door #ANONIEM op 07-02-2012 20:10:51 ]

dinsdag 7 februari 2012 @ 23:34:51 #80

elkravo

Ik heb macro's verwijderd uit mijn spreadsheet.
Echter bij opstarten meld Excel "deze sheet bevat macro's"...

Wat ben ik vergeten?

woensdag 8 februari 2012 @ 01:59:45 #81

qu63

..de tijd drinkt..

quote:
Op dinsdag 7 februari 2012 23:34 schreef elkravo het volgende:
Ik heb macro's verwijderd uit mijn spreadsheet.
Echter bij opstarten meld Excel "deze sheet bevat macro's"...

Wat ben ik vergeten?

Opgeslagen als .xlsm?

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

woensdag 8 februari 2012 @ 17:30:25 #82

#ANONIEM

Fixel, was mijn oplossing wat je wilde?

[ Bericht 22% gewijzigd door #ANONIEM op 08-02-2012 17:30:40 ]

woensdag 8 februari 2012 @ 18:28:41 #83

elkravo

quote:
Op woensdag 8 februari 2012 01:59 schreef qu63 het volgende:

[..]

Opgeslagen als .xlsm?

Heb nog een oudere Excel 2003.... dus nog gewoon XLS

Hoe kom ik van die macro melding af als ik geen macro in de sheet meen te hebben?

woensdag 8 februari 2012 @ 18:29:36 #84

hendrik2009

Module verwijderen. Alt+F11

woensdag 8 februari 2012 @ 18:43:23 #85

elkravo

quote:
Op woensdag 8 februari 2012 18:29 schreef hendrik2009 het volgende:
Module verwijderen. Alt+F11

Thanks!

woensdag 8 februari 2012 @ 19:05:44 #86

qu63

..de tijd drinkt..

quote:
Op woensdag 8 februari 2012 18:28 schreef elkravo het volgende:

[..]

Heb nog een oudere Excel 2003.... dus nog gewoon XLS

Hoe kom ik van die macro melding af als ik geen macro in de sheet meen te hebben?

Dan zou het opgeslagen kunnen zijn als .xlm, maar ik zie dat t al opgelost is

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

donderdag 9 februari 2012 @ 10:01:32 #87

Puckzzz

Bitch please!

En daar is ze weer met een domme vraag!

Dat van die taal vergeet ik ff het liefst zo snel mogelijk

Excel 20110 NL

Ok...ik heb een hele lijst met adressen/locaties van rioolgemaaltjes (meer dan 600) een aantal van die adressen uit de lijst zijn hoofdgemalen. Deze lijst staat op de tab 'installaties'
Nu is het zo dat ik elke maand een export doe uit ons storingssysteem, dan krijg ik dus een hele lijst met storingen die voorgevallen zijn aan de verschillende installaties. Die lijst komt op de eerste tab, waarna ik eruit ga filteren welke storingen verrekenbaar zijn.
Momenteel filter ik dus handmatig dmv de zoekfunctie welke gemalen uit die lijst de hoofdgemalen zijn (want dat is een andere verrekenprijs) en maak de cel lichtrood. Een heel werk

Kan ik in de kolom van tab 1 waar de adressen staan een soort voorwaardelijke opmaak instellen zodat hij meteen de adressen van de hoofdgemalen herkent en de cel lichtrood maakt?

Hey! Het is OK om alleen je eerste twee teennagels te lakken als je van plan bent peeptoes te gaan dragen!

donderdag 9 februari 2012 @ 10:15:52 #88

Deetch

WTF?

Stel lokatie van storing staat in cel A2
De lijst met hoofdgemaallocaties staat op tabblad "gemaal" cellen A2:A20.

met deze formule:
=IF(ISERROR(VLOOKUP(A2,gemaal!$A$2:$A$20,1,FALSE)),"","X")

In het NL =ALS(ISFOUT(VERT.ZOEKEN(A2;gemaal!$A$2:$A$20;1;ONWAAR));"";"X")

bepaal je of de locatie in A2 een hoofdgemaal is. Zo ja dan komt er in de cel een X te staan zo nee dan komt er niks te staan.
De "" en "X" kun je naar believen veranderen in een functie of waarde, bijvoorbeeld de verrekenprijs

Supra Groningam Nihil
Postjubilea: 10.000 15.000 20.000 25.000
Sit jou kop in die koei se kont en wag tot die bul jou kom holnaai
Wat niemand je vertelt over de bioindustrie, geen bloed maar feiten

donderdag 9 februari 2012 @ 12:11:10 #89

actuarisje

Hoihoi,

Ik heb een vraag over het kopieren van een tabblad. Ik heb een sheet met daarin een overzicht van een aantal medewerkers en een aantal 'template'-sheets voor elk functieprofiel. Ik wil nu met een druk op de knop voor alle medewerkers in de lijst een eigen tabblad aanmaken, gebaseerd op hun functieprofiel. Er wordt dus gekeken naar welk functieprofiel in de lijst staat en aan de hand daarvan wordt bepaald van welke 'template'-sheet er een kopie moet worden gemaakt.

Ik heb beovenstaande functionaliteit gebouwd mbv VBA en op zich gaat dat goed. Voor elke medewerker wordt een juist tabblad aangemaakt. Ik zi nu alleen dat het mis gaat bij het printen van deze tabbladen. Bij het kopieren van de template-sheet wordt wel netjes de info uit de sheet meegenomen (tekst, kolombreedtes, gekleurde cellen e.d), maar niet de pagina-instellingen (schaal, marges kop/voettekst e.d).

Mijn vraag is dus: Hoe kan ik mbv VBA een sheet zodanig kopieren dat ook de pagina-instellingen worden meegenomen?

Wat ik nu doe is eerste een nieuw, leeg tabblad aanmaken (Worksheets.Add) en dan de template daarheen copy-pasten. En daarbij gaat het blijkbaar mis, want de sheet krijgt dan de pagina-instellingen van een nieuwe, lege sheet en niet die van de kopieer-bron...

donderdag 9 februari 2012 @ 14:48:22 #90

ralfie

!Yvan eht nioj

quote:
Op donderdag 9 februari 2012 12:11 schreef actuarisje het volgende:
Hoihoi,

Ik heb een vraag over het kopieren van een tabblad. Ik heb een sheet met daarin een overzicht van een aantal medewerkers en een aantal 'template'-sheets voor elk functieprofiel. Ik wil nu met een druk op de knop voor alle medewerkers in de lijst een eigen tabblad aanmaken, gebaseerd op hun functieprofiel. Er wordt dus gekeken naar welk functieprofiel in de lijst staat en aan de hand daarvan wordt bepaald van welke 'template'-sheet er een kopie moet worden gemaakt.

Ik heb beovenstaande functionaliteit gebouwd mbv VBA en op zich gaat dat goed. Voor elke medewerker wordt een juist tabblad aangemaakt. Ik zi nu alleen dat het mis gaat bij het printen van deze tabbladen. Bij het kopieren van de template-sheet wordt wel netjes de info uit de sheet meegenomen (tekst, kolombreedtes, gekleurde cellen e.d), maar niet de pagina-instellingen (schaal, marges kop/voettekst e.d).

Mijn vraag is dus: Hoe kan ik mbv VBA een sheet zodanig kopieren dat ook de pagina-instellingen worden meegenomen?

Wat ik nu doe is eerste een nieuw, leeg tabblad aanmaken (Worksheets.Add) en dan de template daarheen copy-pasten. En daarbij gaat het blijkbaar mis, want de sheet krijgt dan de pagina-instellingen van een nieuwe, lege sheet en niet die van de kopieer-bron...

1	sheet.Copy([before], [after]);

donderdag 9 februari 2012 @ 17:08:26 #91

Barry87

Ik heb het volgende 'probleem':

In kolom C heb ik de namen van producten staan en in kolom H staat bij een gedeelte van deze producten de prijs. Bijvoorbeeld in cel C2 staat het product 'Measuring jug' in cel H2 staat de prijs van 4,60. Dit is prima.

Het probleem is echter dat in bijvoorbeeld C211 ook het product 'Measuring jug' staat maar de cel H211 is leeg.

Kan ik het niet zo instellen dat bij het invullen van 'Measuring jug' in cel C211 de prijs van 4,60 automatisch in cel H211 komt?

Hopelijk kan iemand mij helpen.

donderdag 9 februari 2012 @ 17:14:49 #92

qu63

..de tijd drinkt..

quote:
Op donderdag 9 februari 2012 17:08 schreef Barry87 het volgende:
Ik heb het volgende 'probleem':

In kolom C heb ik de namen van producten staan en in kolom H staat bij een gedeelte van deze producten de prijs. Bijvoorbeeld in cel C2 staat het product 'Measuring jug' in cel H2 staat de prijs van 4,60. Dit is prima.

Het probleem is echter dat in bijvoorbeeld C211 ook het product 'Measuring jug' staat maar de cel H211 is leeg.

Kan ik het niet zo instellen dat bij het invullen van 'Measuring jug' in cel C211 de prijs van 4,60 automatisch in cel H211 komt?

Hopelijk kan iemand mij helpen.

Ken je de functie vert.zoeken()?

It's Time To Shine
[i]What would life be like without rhethorical questions?[/i]

vrijdag 10 februari 2012 @ 20:13:31 #93

elkravo

Ik exporteer een deel van een Excel blad met onderstaande VBA naar een csv-file.
Gevonden op internet, en werkt goed.

Toch ben ik over een ding niet tevreden. In deze export-VBA zit ingebakken een vaste plaats op de harde schijf... in dit geval: C:\Users\Elkravo\Desktop\File.csv

Is deze opdracht te vervangen door iets waarbij ik zelf een plaats kan aanwijzen in het proces.
Deze vraag is ingegeven door het feit dat meerdere personen het sheet op eigen computer gebruiken, en dus de macro niet te specifiek een locatie moet aanwijzen.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Sub export()
     '
     ' export Macro

    Range("A1:E50").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Elkravo\Desktop\File.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

End Sub

[ Bericht 97% gewijzigd door elkravo op 10-02-2012 23:09:45 ]

zaterdag 11 februari 2012 @ 07:25:35 #94

#ANONIEM

saveas = stringwaarde

Stringwaarde in simpel userform vragen

Je kan ook naar de directory browsen en de stringwaarde opbouwen als

stringwaarde = newdir & "Filename"

Of gewoon gebruiken wat er al is

1
2
3

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="csv Files (*.csv), *.csv")
ActiveWorkbook.SaveAs (fileSaveName)

Je kan eventueel ook gebruik maken van het pad ThisWorkbook.path & "\" & "NAAM"

Als je echter een Add-In gebruikt voor de code verwijst ThisWorkbook naar het pad van de Add-In en kun je beter ActiveWorkbook.path gebruiken

[ Bericht 9% gewijzigd door #ANONIEM op 11-02-2012 08:59:22 ]

zaterdag 11 februari 2012 @ 08:43:43 #95

#ANONIEM

quote ipv edit gedrukt

[ Bericht 40% gewijzigd door #ANONIEM op 11-02-2012 08:45:07 ]

zaterdag 11 februari 2012 @ 13:48:15 #96

elkravo

quote:
Op zaterdag 11 februari 2012 07:25 schreef KritischeMassa het volgende:
saveas = stringwaarde

Stringwaarde in simpel userform vragen

Je kan ook naar de directory browsen en de stringwaarde opbouwen als

stringwaarde = newdir & "Filename"

Of gewoon gebruiken wat er al is :
[ code verwijderd ]

Je kan eventueel ook gebruik maken van het pad ThisWorkbook.path & "\" & "NAAM"

Als je echter een Add-In gebruikt voor de code verwijst ThisWorkbook naar het pad van de Add-In en kun je beter ActiveWorkbook.path gebruiken

Hoi KM, alvast bedankt, maar ik krijg het niet helemaal voor elkaar.

Ik geef de voorkeur aan jouw code (waarbij dus een opslaan-als scherm wordt gebruikt)... alleen die moet ik integreren in mijn bestaande code.
Dit lukt mij niet (hij maakt een False.xls) ... want ik weet niet precies hoe die dingen in elkaar moeten geflansd worden. Ben echt een VBA newbie (leek)...

zaterdag 11 februari 2012 @ 13:57:58 #97

#ANONIEM

1
2
3
4
5
6
7
8
9
10
11

Sub export()     '
'export Macro
    Range("A1:E50").Copy
    Workbooks.Add
    ActiveSheet.Paste
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="csv Files (*.csv), *.csv")
    ActiveWorkbook.SaveAs (fileSaveName)
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Als er geen bestandsnaam wordt gesuggereerd en je geeft niets op krijg je idd FALSE terug

dat kun je zo ondervangen:

1
2
3
4
5
6
7
8
9
10
11
12

Sub export()     '
'export Macro
    Range("A1:E50").Copy
    Workbooks.Add
    ActiveSheet.Paste
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="csv Files (*.csv), *.csv")
    If fileSaveName = False Then Exit Sub
    ActiveWorkbook.SaveAs (fileSaveName)
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

[ Bericht 34% gewijzigd door #ANONIEM op 11-02-2012 14:04:56 ]

Forum Opties
Forumhop:
Hop naar:	(afkorting, bv 'KLB')

» digital corner

» software

» digital corner

» software