[Excel] VBA verandert verkeerde regel

maandag 21 maart 2016 12:05

Acties:

0 Henk 'm!

Topicstarter

Hallo,

ik heb in mijn worksheet een worksheet_change macro die 2 macro's na elkaar start. deze kan ik ook handmatig starten.
dit zijn de macro's:

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim x As String
    x = "Te Factureren"
    Dim y As String
    y = "bb transport"
    If ActiveCell.Text = x And ActiveCell.Offset(0, 5).Text = y Then
        Call Nieuwe_Container
        End If
        Call Sorteren
    Application.EnableEvents = True
End Sub

Visual Basic:

Sub Sorteren()

    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom22]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom21]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom20]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom19]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom4]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , CustomOrder:="open/afgevoerd,Zelf in gebruik, Beschikbaar, openstaand,geplaatst,Te Factureren,Gefactureerd,.,Afgehandeld", DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom2]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom15]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom13]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom6]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom72]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

Visual Basic:

End Sub
Sub Nieuwe_Container()
'
' Nieuwe_Container Macro
'

'
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(-1, 3).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 4).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "eigen terrein"
    ActiveCell.Offset(0, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "leeg"
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Beschikbaar"
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Beschikbaar"
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = ""

End Sub

de bedoeling is dat als in een bepaalde cel "Te Factureren" wordt geselecteerd(via een gegevensvalidatielijst), dat er een nieuwe rij wordt gemaakt met cel A,C en J tot S leeg, cel D, E moeten worden gekopieerd, in cel B en F moet beschikbaar staan, in G moet leeg staan en in I moet "eigen terrein" staan.

het probleem is dat als ik nu de macro 'Nieuwe Container' handmatig start, hij de huidige rij alleen F op 'Beschikbaar' zet en de rest van de veranderingen in de rij eronder doet.
bij de automatische macro is het enige probleem dat de cel E de verkeerde waarde is, maar dat is nog handmatig op te lossen.

Ik gebruik excel 2013 voor windows 10

Ik heb geprobeerd wat veranderingen aan te brengen die mij logisch leken, maar mijn vba kennis is daar niet goed genoeg voor, wat hier nu staat heb ik ooit ook een keer van google afgehaald

ik heb het vermoeden dat hij door de worksheet_change sorteert voordat hij de wijzigingen maakt in de nieuwe regel, maar dit weet ik niet zeker, ik heb geprobeerd met EnableEvents in de 'Nieuwe Container' macro dit te voorkomen, maar dat werkte ook niet.

iemand enig idee hoe ik dit op moet lossen?

ps, voor als dit iemand bekent voor komt, over dit bestand heb ik al een keer een topic gehad (macro starten bij bepaalde waarde in de cel)

maandag 21 maart 2016 13:48

Acties:

0 Henk 'm!

ajakkes

👑

Als je in een ander vakje Te Facturen selecteert dan je nu de hele tijd doet, doet ie het ook verkeerd ga ik van uit.

Je gebruikt namelijk aan het begin van je macro activeCel. Welke bij auto start waarschijnlijk altijd hetzelfde is.

👑

maandag 21 maart 2016 14:43

Acties:

0 Henk 'm!

jmvdb22

Topicstarter

Te Factureren wordt maar in 1 cel gebruikt per regel, en in de auto start staat dat de cel 5 naar rechts bb transport moet zijn om Nieuwe_Container te starten, dus dat geeft geen probleem. het grootste probleem zit m juist in het handmatig starten van de macro.

maandag 21 maart 2016 17:09

Acties:

0 Henk 'm!

F_J_K

Moderator CSA/PB

Front verplichte underscores

Microsoft Office

Ik blijf er bij dat dit niet goed gaat werken, althans niet helder. F_J_K in "macro starten bij bepaalde waarde in de cel" etc.

Gebruik Target ipv. ActiveCell in je Change()-functie. Gebruik geen .Select() maar gebruik het betreffende object direct. Tip: gebruik nooit de resultaten van 'macro opnemen' direct. Bijv. ActiveCell.Offset(0, -5).Range("A1").FormulaR1C1 is m.i. erg vervelend geformuleerd en zo dus foutgevoelig.

Dus geen

code:

1 2	ActiveCell.Offset(0, -5).Range("A1").Select ActiveCell.FormulaR1C1 = "Beschikbaar"

Maar
ActiveCell.Offset(0, -5).Range("A1").FormulaR1C1 = "Beschikbaar"
Of eigenlijk, beter
ActiveCell.Offset(0, -5).Value = "Beschikbaar"

Al vind ik hier het gebruik van de Offset van ActiveCell ook niet nodig en wel gevaarlijk. Immers weet je precies welke kolom of regel je wilt bewerken, daar is geen offset (die afhankelijk is van de actieve cel) voor nodig.

En reden dat het fout gaat: in dit geval gaat het ook fout met de ActiveCell. Die verandert continu. Maar ik adviseer ook de rest aan te passen..

'Multiple exclamation marks,' he went on, shaking his head, 'are a sure sign of a diseased mind' (Terry Pratchett, Eric)

woensdag 11 mei 2016 15:15

Acties:

0 Henk 'm!

jmvdb22

Topicstarter

Sorry dat ik een tijdje niet gereageerd heb, was er ff uit ivm mijn opleiding, maar ben nu maar weer eens ingedoken. In de change heb ik nu de eerste activecell in target veranderd, maar als ik dat bij de 2e doe krijg ik Fout 1004, dus die heb ik laten staan. Ook heb ik bij 'nieuwe container' de aanpassingen gemaakt die je zei. Ook heb ik geprobeerd om ipv ".Paste" ".Insert" te gebruiken, maar dit lost mijn probleem helaas niet op.

Dit zijn de codes zoals ze er nu uitzien:

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim x As String
    x = "Te Factureren"
    Dim y As String
    y = "bb transport"
    If Target.Text = x And ActiveCell.Offset(0, 5).Text = y Then
        Call Nieuwe_Container
        End If
        Call Sorteren
    Application.EnableEvents = True
End Sub

Sub Sorteren()

    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom22]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom21]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom20]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom19]"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom4]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , CustomOrder:="open/afgevoerd,Zelf in gebruik, Beschikbaar, openstaand,geplaatst,Te Factureren,Gefactureerd,.,Afgehandeld", DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom2]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom15]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom13]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom6]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields.Add _
        Key:=Range("Tabel1[Kolom72]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

End Sub
Sub Nieuwe_Container()
'
' Nieuwe_Container Macro
'

    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(-1, 3).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Insert
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Insert
    ActiveCell.Offset(0, 4).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 0).Value = "eigen terrein"
    ActiveCell.Offset(0, -2).Value = "leeg"
    ActiveCell.Offset(0, -5).Value = "Beschikbaar"
    ActiveCell.Offset(0, 4).Value = "Beschikbaar"
    ActiveCell.Offset(0, -5).Value = ""
    ActiveCell.Offset(0, 2).Value = ""
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

woensdag 11 mei 2016 15:23

Acties:

0 Henk 'm!

brid

Onze excuses voor het ongemak

ActiveCell is echt echt een ranzige. workbook.worksheet().cells() werkt meestal beter of sla je activecell op in een nieuwe variable. niks leukers dan iemand die tijdens je macro ergens klikt en alles ergens anders verschijnt.

pfff echt lang geleden dat ik met vba bezig ben geweest

redelijke link werken rij/kolom http://www.excel-easy.com.../entire-rows-columns.html

Blijft leuk om te bedenken hoe het ook alweer zit

code:

Sub test()
    Set Mysheet = ActiveSheet
    Set MyCell = ActiveCell
    Myrow = MyCell.Row
    MyColumn = MyCell.Column
    'selecteert eerste row
    Mysheet.Rows(1).Select
    Mysheet.Cells(2, 2).Value = Myrow 'schijf weg in B2
    Mysheet.Cells(3, 3).Value = MyColumn 'schrijf weg in C3
End Sub

Dit moet je toch een eind op weg helpen

Kende Selection change nog niet, je lijkt hem goed te gebruiken. even getest met de volgende code:

code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Target.Value = "Selected"
    Target.Offset(1, 1).Select
    Application.EnableEvents = True
End Sub

Zonder de EnableEvents spamt hij de sheet aardig vol en met maar netjes 1 keer

Waarom gebruik je trouwens de waarde target.text en niet target.value?

[ Voor 82% gewijzigd door brid op 12-05-2016 12:00 ]

DIY NAS, Hoofd PC
Unchain your pc/laptop, buy a SSD!!!!!

donderdag 12 mei 2016 16:59

Acties:

0 Henk 'm!

jmvdb22

Topicstarter

Het gaat erom dat de rij die wordt veranderd eigenlijk behouden blijft en er een nieuwe rij wordt aangemaakt waar dus de standaard gegevens inkomen, lukt dat op deze manier ook?

Volgens mij doet de selection change hier niet echt wat tot nu toe, dat is denk ik vooral de worksheet change, maar er is sws weinig dat echt zn werk doet op het moment, alleen het sorteren werkt, maar ik vermoed dat daar ook het probleem ligt omdat er gesorteerd wordt voordat de macro nieuwe container zn ding heeft afgemaakt.

Als ik .value had staan kreeg ik een foutmelding, nadat ik er .text van maakte kreeg ik die niet meer, vandaar.

vrijdag 13 mei 2016 11:15

Acties:

0 Henk 'm!

brid

Onze excuses voor het ongemak

Het lijkt me onwaarschijnlijk dat de sort functie wordt aangeroepen.

Je kan natuurlijk de sort aanroep ff weg commenten en kijken wat er dan gebeurd of je declareert ranzig een globale boolean en checkt zo of je mag sorteren, maar dat doet EnableEvents al.

Een Sort is volgens mij een eenmalige actie en niet iets wat na een change auto magisch de sortering op nieuw doet...

maar ik krijg echt hoofdpijn van al die offsets, totaal niet te achter halen waar je nu echt in sheet zit.

Neem het begin al:

code:

1
2
3

ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Dit is dus eigenlijk voeg een regel toe onder de regel die boven de huidige staat

Dat kan dus ook zo:

code:

1
2
3

MyRow = ActiveCell.Row
Mycolumn = ActiveCell.Column
Rows(MyRow).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove

Myrow is nu de toe gevoegde row en +1 de originele
Dus als je geen special dingen mee wil kopieren kun je dus gewoon

code:

1	Cells(Myrow,3).value = Cells(Myrow+1,3).value

en nog ff een snell google, complete cell:

code:

1	Cells(Myrow +1, 1).Copy Destination:=Cells(Myrow, 1)

[ Voor 9% gewijzigd door brid op 13-05-2016 12:16 ]

DIY NAS, Hoofd PC
Unchain your pc/laptop, buy a SSD!!!!!

vrijdag 13 mei 2016 13:05

Acties:

0 Henk 'm!

Lustucru

26 03 2016

Om te beginnen haal ik dat dubbele veranderd maar uit de topictitel en fix ik je taalfout:
"Excel VBA veranderd veranderd verkeerde regel" -->[Excel] VBA verandert verkeerde regel

jmvdb22 schreef op donderdag 12 mei 2016 @ 16:59:
Het gaat erom dat de rij die wordt veranderd eigenlijk behouden blijft en er een nieuwe rij wordt aangemaakt waar dus de standaard gegevens inkomen, lukt dat op deze manier ook?

Nee. Ik sluit me aan met bovenstaande heren. Bot gezegd: dit is baggercode. Zelfs al doet die op een gegeven moment wat je wilt dan valt het bij de eerste de beste kleine wijziging weer om. Je zult het gestructureerd moeten aanpakken. Wijzigingen door willen voeren en dan maar laten zitten omdat je een foutmelding krijgt is een no-go.

Stap 1:
Schrijf losse, werkende routines en test die. Voor het aanmaken van een nieuwe rij kun je bv volstaan met:

Visual Basic:

Sub NewRow(bronRij As Long)

    Dim doelrij As Long
    doelrij = bronRij + 1
    
    With ActiveSheet
        .Rows(doelrij).Insert
                
        .Cells(doelrij, 1) = "foo"
        .Cells(doelrij, 2) = .Cells(bronrij, 2)
        .Cells(doelrij, 3) = "bar"
        .Cells(doelrij, 4) = "wat je verder nog verzint"
        .Cells(doelrij, 5) = .Cells(bronRij,5)
    End With
End Sub

Deze routine kun je testen met bv

Visual Basic:

1
2
3

Sub TestNewRow()
    NewRow ActiveCell.Row
End Sub

Stap 2: herschrijf je sorteer macro en test die goed. je code wordt beter leesbaar door gebruik van with:

Visual Basic:

 With ActiveWorkbook.Worksheets("Verhuur").ListObjects("Tabel1").Sort.SortFields
        .Clear
        .Add [...]
        .Add [...]
        .Add [...]
'etc
End With

Stap 3:
Schrijf de worksheet.change handler zoals is voorgeschreven. Dwz: controleer met intersect welke column of rij gewijzigd is, lees de nieuwe waarde uit en bepaal aan de hand van de waarde wat er moet gebeuren. gebruik zinvolle variabelen namen, dus geen x of y.

Roep bij het ontwikkelen de routines nog niet aan maar zet er bv een messagebox in zodat je kunt zien wat wanneer wordt aangeroepen.

Bijvoorbeeld

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsNull(Intersect(Target, Range("A:A"))) Then
        Select Case Target.Text
        Case "mogelijkheid één"
            MsgBox "doe dit"
            MsgBox "doe dat"
        Case "mogelijkheid twee"
            MsgBox "doe dat"
        Case Else
            MsgBox "doe dit bij een andere wijziging"
        End Select
    End If
End Sub

Je zult er dan bijvoorbeeld snel achterkomen dat de copy-macro tot gevolg heeft dat de worksheetchange() routine enkele keren wordt aangeroepen.

Stap 4;
Voeg e.e.a. samen. vervang de msgboxjes door echte functieaanroepen, zorg ervoor dat de copy macro het worksheetchange(0 event niet triggert en voeg basale foutafhandeling toe.

[ Voor 6% gewijzigd door Lustucru op 13-05-2016 13:48 ]

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland

vrijdag 13 mei 2016 14:21

Acties:

0 Henk 'm!

brid

Onze excuses voor het ongemak

Lustucru schreef op vrijdag 13 mei 2016 @ 13:05:
Om te beginnen haal ik dat dubbele veranderd maar uit de topictitel en fix ik je taalfout:
"Excel VBA veranderd veranderd verkeerde regel" -->[Excel] VBA verandert verkeerde regel

Goed uitgebreid verhaal over basic flows/debuggen enzo

[...]

Moet de juiste titel niet zijn "[EXCEL] mijn VBA code verandert niet de regel die ik verwacht"

VBA doet niks verkeerd, die volgt gewoon de instructies van de domme gebruiker op

O ja nog een gratis tip: gebruik de F8 knop (Lijn voor lijn uitvoeren) en Breakpoint als je code andere dingen doet dan je verwacht.

Ook kun je door op een variable met de rechtermuisknop te klikken, een Controle toevoegen.
Op het moment van een breakpoint of het uitvoeren van een regel kun je dan zien welke waarde deze heeft

[ Voor 24% gewijzigd door brid op 13-05-2016 16:50 ]

DIY NAS, Hoofd PC
Unchain your pc/laptop, buy a SSD!!!!!

Onderwerpen

Vraag

Alle reacties