[Excel] Het grote Excel vragen topic #33

donderdag 21 juni 2012 @ 20:40:10 #26

snabbi

vak Q

Ik ben geen held met user forms (en vind dit eigenlijk een oneigenlijk element in Excel)

Vraag 2)
Volgens mij moet je iets maken in de trant van:

quote:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
'xxx
End If
End Sub

CloseMode is een 1 wanneer je zelf gebruik maakt van Unload Me
CloseMode is een 0 wanneer je bijvoorbeeld het kruisje gebruikt.

Via deze wijze kan je hem leiden naar je routine die je gebruikt voor Cancel.

3)

quote:
Public Function BladBestaat(ByVal myName As String) As Boolean
On Error Resume Next
Bladbestaat = (Sheets(myName).Name <> "")
On Error GoTo 0
End Function

Nu kan je gewoon iets doen als:

quote:
Dim MyNewName As String
naamwerkblad:
MyNewName = InputBox("Hoe moet het tabblad heten")
If BladBestaat(MyNewName) Then
MsgBox ("Deze naam bestaat al")
GoTo naamwerkblad
End If
ActiveSheet.Name = MyNewName

donderdag 21 juni 2012 @ 22:01:49 #27

DaFan

quote:
Op woensdag 20 juni 2012 18:01 schreef znarch het volgende:

[..]

Ik faal, hard

Ok

Open VBA editor met Alt-F11.
Voeg een nieuwe Module toe door rechtermuisknop te klikken je filename (VBAProject (xxx) en te kiezen voor Insert -> Module

Plak deze code erin:

1
2
3
4
5

Public Function ExpNormSDist(x As Double, y As Double, z As Double) As Double

ExpNormSDist = Exp(((-1 * x) * y)) * Application.WorksheetFunction.NormSDist(z)

End Function

Dan sluit je de editor.
Nu kan je in Excel dmv

1	=ExpNormSDist(VB1;VB2;C24)

de uitslag bepalen.
Omdat VB1 en VB2 gelijk zijn kan je ook gebruik maken van:

1
2
3
4
5

Public Function ExpNormSDist(z As Double) As Double

ExpNormSDist = Exp(((-1 * Range("VB1").Value) * Range("VB2").Value)) * Application.WorksheetFunction.NormSDist(z)

End Function

En dan de uitkomst ophalen met

1	=ExpNormSDist(C24)

Succes!
Kijk even of je eruitkomt hoé het werkt, en niet óf het werkt

vrijdag 22 juni 2012 @ 08:28:24 #28

xyntarsus

ex nihi omnia creata est

thx voor de info wederom

ik heb voor de foutcontrole (bestaat de bladnaam al, zo ja geef een melding)
de volgende 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

Private Sub CommandButton1_Click()

Item = TextBox1
ActiveSheet.Range("b2").Value = Item

Item = TextBox2
ActiveSheet.Range("b3").Value = Item

Item = TextBox3
ActiveSheet.Range("b4").Value = Item

Item = TextBox4
ActiveSheet.Range("b5").Value = Item

Item = TextBox5
ActiveSheet.Range("f2").Value = Item

Item = TextBox6
ActiveSheet.Range("f3").Value = Item

On Error GoTo fout

ActiveSheet.Name = ActiveSheet.Range("B2")

fout:
     MsgBox ("het nummer bestaat al, voer een nieuw nummer in")
     UserForm1.Hide
     Application.DisplayAlerts = False
     ActiveSheet.Delete
     Application.DisplayAlerts = True
     Sheets("basis").Unprotect
     Sheets("basis").Copy Before:=Sheets("basis")
     Sheets("basis").Protect
     Sheets("basis (2)").Select
     UserForm1.Show

UserForm1.Hide

End Sub

Deze gaat dus lopen als ik op OK klik
Als ik nu een al bestaand project heb geeft hij ook de juiste foutmelding, en hij opent ook netjes
het userform opnieuw.
Hierna gaat het mis, alle nummers die ik invoer neemt hij als 'al bestaand' en ik kom er niet meer uit.

Als ik nu de functie beeindig en opnieuw opstart, blijft hij de fout loopen en kan ik geen nieuwe bladen maken.

Wie kan mij vertellen wat ik fout doe??

vrijdag 22 juni 2012 @ 22:35:42 #29

snabbi

vak Q

quote:
Op vrijdag 22 juni 2012 08:28 schreef xyntarsus het volgende:
thx voor de info wederom

ik heb voor de foutcontrole (bestaat de bladnaam al, zo ja geef een melding)
de volgende code
[ code verwijderd ]

Deze gaat dus lopen als ik op OK klik
Als ik nu een al bestaand project heb geeft hij ook de juiste foutmelding, en hij opent ook netjes
het userform opnieuw.
Hierna gaat het mis, alle nummers die ik invoer neemt hij als 'al bestaand' en ik kom er niet meer uit.

Als ik nu de functie beeindig en opnieuw opstart, blijft hij de fout loopen en kan ik geen nieuwe bladen maken.

Wie kan mij vertellen wat ik fout doe??

Ok, laten we beginnen met een stukje voor de leesbaarheid van je code. Hiermee bedoel ik ook voorkomen dat je onnodig variabelen aanmaakt, danwel de code heel lang maakt

Een verwijziging naar Activesheet is niet nodig, ook is het eigenlijk niet nodig met de range te werken (al vinden veel mensen dit wel zo netjes. Je eerste deel kan je in feite beperken tot:

quote:
[b2] = textbox1
[b3] = textbox2
[b4] = textbox3
[b5] = textbox4
[f2] = textbox5
[f3] = textbox6

Nu naar je echte probleem. Hij loopt je code van boven tot beneden door. Dit houdt in dat hij je code van "fout" altijd uitvoert, ongeacht of het nu goed of fout gaat. Wat je dus moet doen is zorgen dat je code stopt, wanneer het niet fout gaat. In jouw geval krijg je dan zoiets:

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

Private Sub CommandButton1_Click()
On Error GoTo fout
[b2] = textbox1
[b3] = textbox2
[b4] = textbox3
[b5] = textbox4
[f2] = textbox5
[f3] = textbox6
ActiveSheet.Name = Range("B2")
'mag overigens ook zijn ActiveSheet.Name = [b2]
'wanneer het goed gaat moet hij nu stoppen, daarom zet ik hieronder End neer, je kan bijvoorbeeld ook een Goto Einde maken ofzo
On Error GoTo 0
'Wanneer je iets met error handling doet, is het goed om wanneer je klaar bent met het afvangen van je error weer terug te verwijzen naar goto 0 zodat weer de normale Excel error naar voren kan komen.

End
fout:
'en hier je fout handling zoals je dat wil
end sub

maandag 25 juni 2012 @ 13:39:54 #30

xyntarsus

ex nihi omnia creata est

Thx

ik heb het zo opgebouwd, en t lijkt zelfs soepeler te lopen.

nu voel ik me nog meer een amateur

Maar goed, op naar de volgende te bouwen onderdelen
(lijsten genereren, zoekfunctie bouwen en automatisch gegevens invoeren vanuit cellen)

donderdag 28 juni 2012 @ 16:31:18 #31

xyntarsus

ex nihi omnia creata est

en wederom ben ik er met een vraag.

Ik wil in het bestand dat ik aan het bouwen ben nog 2 extra dingen toevoegen.
1. een lijst van alle tabbladen (in mijn geval projectnummers)
waarbij dan een aantal gegevens uit het tabblad worden gezet in een lijst

2. een zoekfunctie waarbij ik een artikelnummer opgeef en die door meerdere tabbladen heen zoekt en kijkt of er in een bepaalde range cellen
(gedefinieerd door een artikelnummer aan het begin van de regel) niet lege cellen zijn.
deze regels met gegevens (dus de regels waarin in gegevens zijn ingevoerd) moet dan in een lijst
komen die weer begint met het projectnummer en dan de rest van de regel erachter zet
(en voor elk product in de zoekopdracht een lijst maakt)

Ik heb bij deze twee echter geen idee hoe ik moet beginnen

Wie helpt mij???

donderdag 28 juni 2012 @ 22:24:19 #32

snabbi

vak Q

quote:
Op donderdag 28 juni 2012 16:31 schreef xyntarsus het volgende:
en wederom ben ik er met een vraag.

Ik wil in het bestand dat ik aan het bouwen ben nog 2 extra dingen toevoegen.
1. een lijst van alle tabbladen (in mijn geval projectnummers)
waarbij dan een aantal gegevens uit het tabblad worden gezet in een lijst

2. een zoekfunctie waarbij ik een artikelnummer opgeef en die door meerdere tabbladen heen zoekt en kijkt of er in een bepaalde range cellen
(gedefinieerd door een artikelnummer aan het begin van de regel) niet lege cellen zijn.
deze regels met gegevens (dus de regels waarin in gegevens zijn ingevoerd) moet dan in een lijst
komen die weer begint met het projectnummer en dan de rest van de regel erachter zet
(en voor elk product in de zoekopdracht een lijst maakt)

Ik heb bij deze twee echter geen idee hoe ik moet beginnen

Wie helpt mij???

Voor het eerste deel is er zover ik weet geen normale formule die je daar bij kunt helpen en moet je dus naar VBA en krijg je iets in de trant van:

1
2
3

For i = 1 To Sheets.Count
ActiveCell.Offset(i - 1, 0).Value = Sheets(i).Name
Next i

Het eerste deel is gewoon een loop maken van 1 tot het aantal sheets dat je hebt. Vervolgens ga je voor elke sheet vanaf je huidige cel de namen onder elkaar opslaan. (De offset zorgt ervoor dat je i-cellen naar beneden vanaf de huidige cel iets wegschrijft).

Het tweede deel van je vraag begrijp ik niet helemaal. Kan je niet beter alle activiteiten op 1 tabblad opslaan en wanneer je iets voor een specifiek project wilt zien daar op filteren of een view daarvoor maken. Het verspreiden over al die tabbladen lijkt me eigenlijk niet handig.

vrijdag 29 juni 2012 @ 10:00:44 #33

xyntarsus

ex nihi omnia creata est

dank je, dat eerste deel is al erg nuttig,
kan ik daar dan ook, als ik die lijst eenmaal gegenereerd heb, gegevens uit dat specifieke blad achter hangen op een geautomatiseerde manier

de code die ik nu heb:

1
2
3
4
5
6
7
8

Private Sub CommandButton4_Click()

  Sheets.Add Before:=Sheets("totaal")
  ActiveSheet.Name = "Projectenlijst"

For i = 4 To Sheets.Count -1
ActiveCell.Offset(i - 1, 1).Value = Sheets(i).Name
Next i

Wat ik ook graag wil is een aantal bladen uitsluiten in de lijst (totaalblad en 'basis'tabel)
Kan dat ook?
boerenlogica zegt: als bladnaam = totaal, dan niets doen en doorgaan met de volgende
edit: ik heb hem nu in het 4e blad laten beginnen en het laatste niet meenemen, dan krijg ik in ieder geval te zien wat ik wil. Weet alleen niet of dit de beste oplossing is

voor het tweede deel van mijn vraag:
omdat ik ook een lijst per project wil kunnen uitdraaien, en ik iedere keer dezelfde tabel heb
voor een project (in de rijen de producten en in de kolommen 52 weken) wordt het heel onoverzichtelijk om dat in 1 blad te doen.
vandaar de keuze om voor elk project (pakweg 50 tegelijk meestal) een apart tabblad te maken, mijn 1e blad is dan een totaalblad dat optelt door de bladen heen.

Ik heb laatst een vergelijkbare opzet gezien, maar helaas krijg ik daar de code niet van :S

[ Bericht 7% gewijzigd door xyntarsus op 29-06-2012 10:10:59 (trial and error werkt :P) ]

vrijdag 29 juni 2012 @ 11:56:50 #34

Deetch

WTF?

Zoiets? Je moet zelf nog even met de variabele j puzzelen om de lijst op de goeie plek te krijgen.
Waarom begin je trouwens pas vanaf sheet 4 te tellen?

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

Sub test()

j = 0

For i = 4 To Sheets.Count -1
    If Sheets(i).Name = "totaalblad" Or Sheets(i).Name = "basistabel" Then
    j = j
    Else
    ActiveCell.Offset(j, 1).Value = Sheets(i).Name
    j = j + 1
    End If
Next i

End Sub

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

vrijdag 29 juni 2012 @ 13:15:53 #35

xyntarsus

ex nihi omnia creata est

omdat de eerste drie bladen de bladen zijn die ik niet wil mee tellen, dus ik had het eigenlijk omzeild

dat stukje 'if' functie is denk ik de nette manier

zo lukt het in ieder geval wel

blijven de punten van het invullen van andere gegevens uit de betreffende bladen en de zoekfunctie over...
na wat zoek en stoeiwerk denk ik dat ik met vert.zoeken een eind moet kunnen komen,
maar dan moet ik dat over meerdere bladen trekken en vervolgens van de gevonden regels een kopie in een nieuw blad zetten (en dan alle gevonden regels in hetzelfde blad onder elkaar)

vrijdag 29 juni 2012 @ 13:33:22 #36

Deetch

WTF?

Maar als iemand dan je bladen husselt dan gaat dat dus wel mis.

Als je veel uitzonderingen hebt is het misschin mooier om met case select te werken in plaats van de If....then...else
Dus in plaats van het hele stuk code tusen IF en END IF doe je onderstaande code

1
2
3
4
5
6
7

Select Case Sheets(i).Name
Case "totaal", "test"
j = j
Case Else
ActiveCell.Offset(j, 1).Value = Sheets(i).Name
j = j + 1
End Select

Aangezien je een lijst wilt op basis van projectnummer en alle tabbladen projectnumemrs zijn zou je een vert.zoeken kunnen combineren met indirect?

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

vrijdag 29 juni 2012 @ 13:46:10 #37

xyntarsus

ex nihi omnia creata est

met indirect?
nu ben ik overvraagd....

vrijdag 29 juni 2012 @ 13:53:57 #38

Deetch

WTF?

Stel naam van tabblad is "project1"
Om nu een waarde uit dat tabblad te halen doe je ='project1'!C2

Echter als je nou de naam van het tabblad variabel wilt maken omdat je al een lijst hebt met alle tabbladnamen dan kun je met INDIRECT dat bereiken.

Voorbeeld:
zet de naam van het tabblad op een ander blad in cel A1 (bijvoorbeeld)
en zet in de cel eronder de volgende formule

=indirect("'"& A1 &"'!C2") (let op alle leestekens)

deze haalt de waarde uit cel C2 van het tabblad waarvan de naam in A1 staat.

met het & teken kun je tekststrings aan elkaar plakken. Je plakt hier dus een ' voor de naam van het tabblad (A1) en erachter '!C2 voor het afsluiten van de tabblad naam en de verwijzing naar de cel.

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

vrijdag 29 juni 2012 @ 14:15:30 #39

xyntarsus

ex nihi omnia creata est

ok thx, dit werkt, ik kan nu een projectenlijst maken en updaten

enige wat hij nu niet doet, is projecten die er niet meer zijn weghalen,
is er een mogelijkheid dat ik dta kan invoegen?

vrijdag 29 juni 2012 @ 14:48:22 #40

Deetch

WTF?

quote:
Op vrijdag 29 juni 2012 14:15 schreef xyntarsus het volgende:
ok thx, dit werkt, ik kan nu een projectenlijst maken en updaten

enige wat hij nu niet doet, is projecten die er niet meer zijn weghalen,
is er een mogelijkheid dat ik dta kan invoegen?

Ik ben er van uitgegaan dat de sheetnamenlijst in b1 en daaronder staat, anders in de activesheet regel b1 veranderen in de gewenste cel
De hele regel van de sheet die niet meer bestaat wordt verwijderd

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

Sub sheetbestaatnietmeer()

On Error Resume Next

ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown)).Select

For Each c In Selection
If Sheets(c.Value).Name = "" Then c.EntireRow.Delete
Next c
On Error GoTo 0

End Sub

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

vrijdag 29 juni 2012 @ 21:39:16 #41

snabbi

vak Q

quote:
Op vrijdag 29 juni 2012 10:00 schreef xyntarsus het volgende:
voor het tweede deel van mijn vraag:
omdat ik ook een lijst per project wil kunnen uitdraaien, en ik iedere keer dezelfde tabel heb
voor een project (in de rijen de producten en in de kolommen 52 weken) wordt het heel onoverzichtelijk om dat in 1 blad te doen.
vandaar de keuze om voor elk project (pakweg 50 tegelijk meestal) een apart tabblad te maken, mijn 1e blad is dan een totaalblad dat optelt door de bladen heen.

Ik heb laatst een vergelijkbare opzet gezien, maar helaas krijg ik daar de code niet van :S

Tja eigenlijk blijf ik wel van mening dat je eigenlijk al je data in 1 tabblad moet zetten. Wanneer je verschillende dwarsdoorsnedes wilt maken van je gegevens set kan je dat veel makkelijker via een pivot tabel regelen. Dan kan je tenminste zeggen ik wil alles van 1 project zien, of 1 product over alle projecten heen.

Ook scheelt dit heel veel macro gedoe, wat in mijn ogen er alleen maar voor zorgt dat je programma onbeheersbaar wordt.

zaterdag 30 juni 2012 @ 12:54:01 #42

DaFan

Access

maandag 2 juli 2012 @ 08:20:23 #43

Deetch

WTF?

quote:
Op zaterdag 30 juni 2012 12:54 schreef DaFan het volgende:
Access

Dat is eigenlijk wel beter voor xyntarsus' gebruik. Maar ja, access is eng

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 2 juli 2012 @ 09:30:33 #44

DaFan

quote:
Op maandag 2 juli 2012 08:20 schreef Deetch het volgende:

[..]

Dat is eigenlijk wel beter voor xyntarsus' gebruik. Maar ja, access is eng

Faler

maandag 2 juli 2012 @ 10:43:22 #45

xyntarsus

ex nihi omnia creata est

access is niet eng hoor

maar acces is niet beschikbaar op het netwerk hier

moet het doen met word en excel....
en wat betreft de aparte tabbladen, ook dat is een soort van
'opgelegde eis' :S

pivot tables zouden inderdaad kunnen als alles in 1 blad stond.

maandag 2 juli 2012 @ 11:02:31 #46

Deetch

WTF?

quote:
Op maandag 2 juli 2012 10:43 schreef xyntarsus het volgende:
access is niet eng hoor

maar acces is niet beschikbaar op het netwerk hier
moet het doen met word en excel....
en wat betreft de aparte tabbladen, ook dat is een soort van
'opgelegde eis' :S

pivot tables zouden inderdaad kunnen als alles in 1 blad stond.

zucht, IT op de werkplek

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 2 juli 2012 @ 11:07:58 #47

xyntarsus

ex nihi omnia creata est

idd, maja, ik zal t moeten doen met wat we hebben

kan thuis wel een Access dbase bouwen in office 2010, maar daar gaan we in
excel 2003 weinig aan hebben

:P

zal dus wat meer moeite moeten doen, maar heb er wel vertrouwen is dat t gaat lukken...

(denk ik....)

maandag 2 juli 2012 @ 22:14:56 #48

snabbi

vak Q

Mwa je kan vanuit Excel gewoon een koppeling maken naar je Access database.
Daarnaast kan je bij het dubbel-klikken in je pivot tabel je betreffende benodigde tabblad creëren. Het maakt in ieder geval die zoekfunctie makkelijker (query) ipv via vertikaal zoeken over alle tabbladen of een ctrl f functie via macro's.

dinsdag 3 juli 2012 @ 08:08:35 #49

xyntarsus

ex nihi omnia creata est

Dat klopt, maar blijf het probleem bestaan dat op t netwerk hier geen access beschikbaar is

Is die zoekfunctie overigens wel mogelijk??

[ Bericht 15% gewijzigd door xyntarsus op 03-07-2012 08:48:05 ]

dinsdag 3 juli 2012 @ 13:32:08 #50

ralfie

!Yvan eht nioj

Zoiets voor de zoekfunctie?

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

Sub zoek(ByRef Sheet As Worksheet, ByRef Targetcell As Range, ByRef qrySearch As String, ByRef numcolumns As Integer)
    Dim zoekrange As Range, lastrow As Integer, cell As Range
    Set zoekrange = Sheet.Range("A:A")'Waar gaan we zoeken?
    Set cell = Sheet.Cells(1, 1)'De eerste cell in de kolom
    lastrow = 0
    Do
        Set cell = zoekrange.Find(qrySearch, cell, xlValues, xlPart, xlByColumns, xlNext)
        If cell Is Nothing Then Exit Sub'zoekopdracht nergens gevonden
        If cell.Row < lastrow Then Exit Sub'voorbij einde van kolom
        lastrow = cell.Row
        Targetcell.Value = Sheet.Name
        If WorksheetFunction.CountA(Sheet.Rows(lastrow)) > 1 Then'Er zitten niet-lege cellen in deze rij
            Sheet.Range("A" & lastrow).Resize(ColumnSize:=numcolumns).Copy Targetcell.Offset(ColumnOffset:=1).Resize(ColumnSize:=numcolumns)
            Set Targetcell = Targetcell.Offset(Rowoffset:=1)
        End If
    Loop
End Sub

Je zoekt dan bijvoorbeeld:

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

Sub test()
dim rngDoelwit as Range
dim strQry as string
dim numCols as integer

'hoeveel kolommen moeten er gekopieerd worden
numCols = 10
'Je zoekopdracht
strQry = "1*"
'Waar moeten de resultaten komen
set rngDoelwit = ActiveCell

For i = 4 To Sheets.Count -1
    If Sheets(i).Name <> "totaalblad" And Sheets(i).Name <> "basistabel" Then
        zoek Sheets(i), rngDoelwit, strQry, numCols
    End if
Next i
End Sub

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

» digital corner

» software

» digital corner

» software