Find functie in mijn tabel (list object) geeft fout 91 tijdens uitvoering

[RickBu]

Beste helpers,

Ik zit met het volgende: in een Tabel genaamd "Orderdata" heb ik informatie en de weekdagen van het jaar uiteengezet. Het idee is om hiermee een planning op te zetten. De input voor iedere regel die aan de tabel wordt toegevoegd komt vanuit een formulier. Wat ik wil is de leverdatum die ingevoerd is in het formulier, terugvinden in een positie (range) op de bovenste rij (HeaderRowRange) van de tabel. Om vervolgens shapes (rectangles) toe te kunnen voegen op regelniveau die de duur van een bepaalde bewerking symboliseren. Hun lengte wordt vervolgens gebruikt om tot een startdatum te komen. Een voorbeeld van hoe ik de shapes voor mij zie is hieronder toegevoegd. Ik weet dat het mogelijk is dit via de grafiekmodule van Excel te doen maar die module mist flexibiliteit en complexiteit die ik nodig heb.



Ik loop vast bij het vinden en toewijzen van een range aan de overeenkomende datum.

Ik heb dit geprobeerd in een array omgeving en daar ging het vrij eenvoudig. Om het gebruiksgemak te vergroten wil ik toch een Tabel gebruiken. Tijdens het stapsgewijs uitvoeren van mijn code blijf ik steeds foutcode 91: Objectvariabele of blokvariabele With is niet ingesteld krijgen. Ik heb al heel wat uren op internet gezocht naar oplossingen die de Find methode gebruiken in een Tabel omgeving maar heb tot op heden nog geen oplossing gevonden. Zelf ben ik helaas nog niet kundig genoeg om het op te lossen. Ik vraag me daardoor ook af of de Find methode wel de meest geschikte is voor mijn toepassing. Ik hoop dat ik de situatie voldoende helder heb toe kunnen lichten.

De code die ik tot dusver heb geschreven voor het invoeren van het formulier is de volgende:

Sub CommandButton1_Click()

'Verwerken van eerste 4 tekstvakken + datum

    Dim PlanningSheet As Worksheet
    Dim Orderdata As ListObject
    Dim TabelregelOD As ListRow
    
    Set PlanningSheet = Sheets("Planning")
    Set Orderdata = PlanningSheet.ListObjects("Orderdata")
    Set TabelregelOD = Orderdata.ListRows.Add

    TabelregelOD.range(1, 1).Value = Me.TextBox1.Value
    TabelregelOD.range(1, 2).Value = Me.TextBox2.Value
    TabelregelOD.range(1, 3).Value = Me.TextBox3.Value
    TabelregelOD.range(1, 4).Value = Me.TextBox4.Value

    TabelregelOD.range(1, 5).Value = Me.DTPicker1.Value

'Verwerken van datum naar een positie op de tabel

    Dim Jaarlengte As range
    Dim Datum As range
  
    Set Jaarlengte = Orderdata.HeaderRowRange
    Set Datum = Jaarlengte.Find(What:=DTPicker1.Value, MatchCase:=True, LookAt:=xlWhole)

        'Hier onstaat de foutmelding
    Dim LeverDatum As Shape
    Set LeverDatum = Datum.AddShape(msoShapeRectangle, Datum.Left, Datum.Top, Datum.Width, Datum.Height)
    
    LeverDatum.Fill.ForeColor.RGB = rgbHotPink
        
End Sub

Ik ben er inmiddels achter dat het vaststellen van de variabele "Datum" problemen oplevert in de code, ik krijg het alleen niet opgelost. Ik heb voor de zekerheid het bestand zelf ook toegevoegd. Ik ben benieuwd of jullie mij hierbij kunnen helpen, in ieder geval alvast bedankt!

Groeten,
Rick

Bekijk bijlage Vernieuwde planning versie 1.3_met macro.xlsm

 

[edmoor]

Ik denk dat je van de DatePicker een String terug krijgt.
Die zal je dan eerst naar een datumwaarde moeten converteren voordat je ermee op datums kan zoeken.

 

[SjonR]

Zoals Edmoor al aangaf is de datum uit de datepicker niet te matchen met de datums in de headerrow. ( vraag me niet waarom niet ).

Verder heb je niet bepaald op welke rij de Shape moet komen te staan. En als je toch een hele cel afplakt met een shape, kan je volgens mij net zo goed direct de cel de kleur van de shape geven. Maar dat gezegd hebbend, is dit wat ik er van heb gebakken

Sub CommandButton1_Click()

    Dim PlanningSheet As Worksheet
    Dim Orderdata As ListObject
    Dim TabelregelOD As ListRow
    Dim i As Integer
    Set PlanningSheet = Sheets("Planning")
    Set Orderdata = PlanningSheet.ListObjects("Orderdata")
    Set TabelregelOD = Orderdata.ListRows.Add
    
        For i = 1 To 4
            TabelregelOD.Range(1, i).Value = Controls("TextBox" & i).Value
        Next

    TabelregelOD.Range(1, 5).Value = Me.DTPicker1.Value

    Dim Datum As Long
    Dim cel As Range

    Datum = PlanningSheet.Rows(3).Find(What:=Day(DTPicker1.Value) & "-" & Month(DTPicker1.Value) & "-" & Year(DTPicker1.Value), MatchCase:=True, LookAt:=xlWhole).Column

    Dim LeverDatum As Shape
    Set cel = TabelregelOD.Range(1, Datum)
    Set LeverDatum = PlanningSheet.Shapes.AddShape(msoShapeRectangle, cel.Left, cel.Top, cel.Width, cel.Height)
    
    LeverDatum.Fill.ForeColor.RGB = rgbHotPink
        
End Sub

 

[VenA]

De gegevens in de headerrow van een listobject zijn altijd tekstwaarden.

 

[SjonR]

Dat had ik moeten weten. Met die opgefriste kennis ( dank @ VenA) kan deze regel dan dus wel zo:

Datum = PlanningSheet.Rows(3).Find(What:=CStr(DTPicker1.Value), MatchCase:=True, LookAt:=xlWhole).Column

 

[RickBu]

Goedemorgen! Bedankt voor jullie snelle reacties! Ik ben erg blij met jullie hulp, ik gok dat het mij nog heel veel tijd had gekost om hier achter te komen.

@ SjonR Ik was me er van bewust dat ik nog een manier moest gaan vinden om de rij voor de shapes te achterhalen en te gebruiken, dat had ik even uitgesteld omdat dit me al zoveel kopzorgen opleverde Fijn dat je dat er meteen in verwerkt hebt, ook de loop voor de tekstvakken is handig! Voor nu ben ik geholpen en kan ik verder.

Ik vraag me alleen nog af of het niet mogelijk is om in de zoekrange van de findregel de HeaderRowRange te plaatsen, want als ik het goed begrijp zal Excel de gehele row doorzoeken toch? Ik lees namelijk regelmatig dat het programma traag kan worden als het een hele Row moet doorspitten of valt dat in dit geval allemaal wel mee?

Nogmaals bedankt!

 

[SjonR]

Of het echt sneller is weet ik niet. maar hier de optie met het doorzoeken van de HeaderRowRange

Sub CommandButton1_Click()

    Dim PlanningSheet As Worksheet
    Dim Orderdata As ListObject
    Dim TabelregelOD As ListRow
    Dim i As Integer
    Dim jaarlengte As Range
    Dim Datum As Long
    Dim cel As Range
    Dim LeverDatum As Shape
    
    Set PlanningSheet = Sheets("Planning")
    Set Orderdata = PlanningSheet.ListObjects("Orderdata")
    Set TabelregelOD = Orderdata.ListRows.Add
    Set jaarlengte = Orderdata.HeaderRowRange
    
        For i = 1 To 4
            TabelregelOD.Range(1, i).Value = Controls("TextBox" & i).Value
        Next

    TabelregelOD.Range(1, 5).Value = Me.DTPicker1.Value
   
    Datum = jaarlengte.Find(What:=CStr(DTPicker1.Value), MatchCase:=True, LookAt:=xlWhole).Column

   
    Set cel = TabelregelOD.Range(1, Datum)
    Set LeverDatum = PlanningSheet.Shapes.AddShape(msoShapeRectangle, cel.Left, cel.Top, cel.Width, cel.Height)
    
    LeverDatum.Fill.ForeColor.RGB = rgbHotPink
        
End Sub

 

[RickBu]

Perfect zeg, dat valt dan weer alles mee. Ik ga zelf maar weer eens aan de slag!

Merci!

 

[SjonR]

succes ermee :thumb: