Foutmelding verticaal zoeken Excel VBA naar vasttijdstip

[Copitano]

Mijn vraag
Ik heb een vrij groot macro gemaakt waarvan alle onderdelen goed lopen op een na. En dat is nu juist het eerste stukje waar bij in het actieve sheet het regelnummer met tijdstip (05:30) moet worden gezocht.
Het startpunt kan per dag verschillen op basis van het tijdstip waarop data via een .csv geïmporteerd wordt in excel.
Gezocht moet worden in kolom A.
Relevante software en hardware die ik gebruik
Ik werk met Excel 2010 (ja ik weet het "heel oud maar het werkt voor mij prima")
De datum tijd loopt op met 5 minuten tussen elke meting.

Wat ik al gevonden of geprobeerd heb
Ik heb een macro opgenomen met de functie "zoeken" in de takbalk van Excel. De zoekfunctie werkt rechtstreeks met de functie "zoeken" in de taakbalk van het tabblad prima. Echter zodra ik de opgenomen macro vanuit VBA draai krijg ik de foutmelding "Objectvariabele of Blokvariabele With is niet ingesteld"
De opgenomen macro ziet er zo uit

code:

 Sub Zoek0530()
'
    Columns("A:A").Select
    Selection.Find(What:="05:30", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Rij = ActiveCell.Row
End Sub

Dat heb ik na flink wat uurtjes googlen zo aangepast

code:

Sub Opschonen()

With Sheets("Blad1")
Dim c As Range

Set c = Range("A:A")

    Selection.Find(What:="05:25", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


    Rij = Active.cell.Row

Maar ik begrijp het nog niet helemaal (of helemaal niet), want ook daar krijg ik nog steeds dezelfde foutmelding.

Wie helpt mij in de goede richting

[ Voor 4% gewijzigd door Copitano op 08-08-2022 22:20 ]

[Joep]

https://docs.microsoft.co...rface-help/with-statement

[Copitano]

Joep schreef op maandag 8 augustus 2022 @ 22:18:
https://docs.microsoft.co...rface-help/with-statement

Ik heb wat dingetjes al geprobeerd, maar waarschijnlijk zie ik iets simpels over het hoofd. Zal wel komen omdat het jargon wat gebruikt wordt is voor mij soms onbegrijpelijk is. Mijn Engels is al 45 jaar aan slijtage onderhevig
Morgen verder

[heuveltje]

Was er nooit een ster in, en al even geleden dat ik in vba/macro bezig ben geweest.

Maar Als je toch in de actieve sheet aan het zoeken bent, waarom heb je dan uberhaubt dat with statement nodig ?'

En het origineel selecteerd een reeks cellen ( Columns("A:A").Select ) waar het naar ik vanuit ga in gaat zoeken. Maar jij defineert een range ( C) maar doet er vervolgens niks mee ?

[ Voor 39% gewijzigd door heuveltje op 08-08-2022 23:10 ]

[Teun_2]

1) kan je die with niet gewoon weglaten?
2) waarom zou je hier een macro voor gebruiken? Een csv kan je prima importeren met powerquery en rijen uitfilteren op basis van een vergelijking is met de nieuwe FILTER functie ook kinderspel.

#fronttegenonnodigvbagebruik

[GRDavies75]

Teun_2 schreef op dinsdag 9 augustus 2022 @ 07:31:
1) kan je die with niet gewoon weglaten?
2) waarom zou je hier een macro voor gebruiken? Een csv kan je prima importeren met powerquery en rijen uitfilteren op basis van een vergelijking is met de nieuwe FILTER functie ook kinderspel.

#fronttegenonnodigvbagebruik

Power query is er wel als add-in voor 2010.
Twijfelachtig of dat ook voor de nieuwerwetse functie FILTER geldt.

[dix-neuf]

Copitano,
Als je de gezochte cel hebt gevonden en geselecteerd, wat wil je er dan mee?

[Lustucru]

De foutmelding ontstaat als find niets vindt en je toch activate aanroept aan het einde van de regel. Verander e.e.a. In iets als:

code:

Set foundCell = range(“A:A”).find(…)

If not foundCell is nothing then
Rij=foundCell.row
[doe je ding met foundcell]
Else
[doe iets anders]
End if

[ Voor 32% gewijzigd door Lustucru op 09-08-2022 15:34 ]

[Copitano]

heuveltje schreef op maandag 8 augustus 2022 @ 23:06:
Was er nooit een ster in, en al even geleden dat ik in vba/macro bezig ben geweest.

Maar Als je toch in de actieve sheet aan het zoeken bent, waarom heb je dan uberhaubt dat with statement nodig ?'

Ja, daar snap ik ook niets van, maar die foutmelding (fout 91) krijg ik nu eenmaal. Dus daar moet ik mee dealen.

En het origineel selecteerd een reeks cellen ( Columns("A:A").Select ) waar het naar ik vanuit ga in gaat zoeken. Maar jij defineert een range ( C) maar doet er vervolgens niks mee ?

Ja, ik heb daar voorbeelden van gevonden via google, maar ik begrijp die waarschijnlijk niet helemaal. Dat is ook de reden dat ik de vraag hier maar heb gesteld. Dat in de hoop dat iemand mij kan uitleggen hoe het dan wel moet.

[Copitano]

dix-neuf schreef op dinsdag 9 augustus 2022 @ 09:23:
Copitano,
Als je de gezochte cel hebt gevonden en geselecteerd, wat wil je er dan mee?

Het gaat mij om het regelnummer. vervolgens regel 2 t/m regel x verwijderen. Regel x+1 wordt dan de nieuwe regel 2. Regel x kan per dag verschillen afhankelijk van hoe laat de imports zijn gemaakt. Die imports bevatten per 5 minuten de zonkracht in Watt/m² en de stralingssom in Joules/cm². Dat voor vijf omliggende KNMI stations.
De overige macro's verwijderen alle regels vanaf 200 zodat alle tabellen een gelijk format hebben. De volgende macro wordt met een Call aangeroepen en maakt er mooie grafieken van .
Die macro's lopen allemaal als een zonnetje . Alleen dat eerste stukje moet ik nu steeds handmatig doen

[dix-neuf]

Copitano schreef op dinsdag 9 augustus 2022 @ 14:07:
Het gaat mij om het regelnummer. vervolgens regel 2 t/m regel x verwijderen. Regel x+1 wordt dan de nieuwe regel 2.

Dan denk ik dat je het beter op een andere manier kunt aanpakken, bv. zo (met in E1 de te zoeken tijd in kolom A en met "Blad1" als naam van het blad):

code:

Sub Verwijder()
Dim m As Integer
With Sheets("Blad1")
If WorksheetFunction.CountIf(.Columns(1), .Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1))
.Rows(2 & ":" & m).EntireRow.Delete
End If
End With
End Sub

[ Voor 22% gewijzigd door dix-neuf op 09-08-2022 18:45 ]

[Copitano]

dix-neuf schreef op dinsdag 9 augustus 2022 @ 15:27:
[...]

Dan denk ik dat je het beter op een andere manier kunt aanpakken, bv. zo (met in E1 de te zoeken tijd in kolom A en met "Blad1" als naam van het blad):

code:

Sub Verwijder()
Dim m As Integer
With Sheets("Blad1")
If WorksheetFunction.CountIf(.Columns(1), Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1))
.Rows(2 & ":" & m).EntireRow.Delete
End If
End With
End Sub

Kan het zo zijn dat het datumformat in de kolom waarin gezocht wordt er voor zorgt dat niets gevonden wordt?

In het verleden heb ik wel vaker gedonder gehad met dat datumformat. Vooral in bijvoorbeeld oktober/november. Excel gaat in die periode in dan de mist in bij het selecteren van periodes. Ik heb dat ook nooit echt kunnen oplossen.
In de zoekkolom is sprake van d:m:jjjj uu:mm in de celeigenschappen geeft Excel aan: Aangepast.
Die laatste heb ik overigens tijdens deze ploetertocht ook al eens ingevuld als zoekterm met ook nul op het rekest.

[ Voor 31% gewijzigd door Copitano op 09-08-2022 17:31 ]

[Copitano]

dix-neuf schreef op dinsdag 9 augustus 2022 @ 15:27:
[...]

Dan denk ik dat je het beter op een andere manier kunt aanpakken, bv. zo (met in E1 de te zoeken tijd in kolom A en met "Blad1" als naam van het blad):

code:

Sub Verwijder()
Dim m As Integer
With Sheets("Blad1")
If WorksheetFunction.CountIf(.Columns(1), Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1))
.Rows(2 & ":" & m).EntireRow.Delete
End If
End With
End Sub

Heb um een beetje aangepast op de rest van de Macro('s). Ziet er nu zo uit:

code:

'Sub Verwijder()
Dim m As Integer
Dim Tijd As Date
Dim Datum As Date
Datum = InputBox(Prompt:="Welke datum wil je ophalen?", Title:="Datum", Default:="d:m:jjjj")
Range("E1").Value = Datum & " " & "05:25"
With Sheets("SS" & " " & Periode)
If WorksheetFunction.CountIf(.Columns(1), Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1))
.Rows(2 & ":" & m).EntireRow.Delete
End If
End With
    Range("E1").Select
    Selection.ClearContents
'End Sub

En works like a charm Dat gaat mij een heleboel tijd schelen
@dix-neuf @heuveltje @GRDavies75 @Joep @Lustucru @Teun_2 dank voor jullie aandacht en meedenken

[dix-neuf]

Zoals je het hebt gewijzigd moet je telkens vóór uitvoering van de macro, in de macro de tijd aanpassen. Dat lijkt mij niet wenselijk, maar als je daar zelf geen problemen mee hebt…
Datum en tijd via een inputbox laten invoeren is vragen om moeilijkheden. Bovendien zal Excel een datum als bv. 9 augustus als 8 september beschouwen. Op de invoer behoor je in de macro dus te controleren en die - indien nodig - aan te passen. Als je nog niet veel ervaring hebt met vba, is dat niet eenvoudig.
De twee regels die je onderaan toevoegde:

code:

Range("E1").Select
Selection.ClearContents

kun je verkorten tot

code:

.Range("E1").ClearContents

en omdat E1 tot hetzelfde blad behoort als de andere cellen die in de macro worden vermeld, moet die opdracht vóór 'End With' geplaatst worden.

In mijn macro verzuimde ik in de regels 4 en 5 een punt en een 0 toe te voegen. Die regels moeten gewijzigd worden als volgt:

code:

If WorksheetFunction.CountIf(.Columns(1), .Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1), 0)

[Copitano]

dix-neuf schreef op dinsdag 9 augustus 2022 @ 19:59:
Zoals je het hebt gewijzigd moet je telkens vóór uitvoering van de macro, in de macro de tijd aanpassen.

Als je tijdstip 05:30 bedoeld, dan is dat een vast tijdstip. Die kan dus in de macro vast worden opgenomen. Het enige wat veranderd is m. Daar is precies de zoekactie voor bedoeld om dat 05:30 elke dag op een andere regel komt te staan en m daarop aangpast moet worden voor het verwijderen van de juiste regels.

Datum en tijd via een inputbox laten invoeren is vragen om moeilijkheden.

Bij het testen met "blad1" liep dat als een zonnetje. Maar ja, dat was 8-8. Daar kan niet zoveel mee misgaan. Met 9-8 dus nog wel. Mijn enthousiasme was dus iets te vroeg. Omdat ik de macro elke dag op 5 verschillende tabbladen gebruik moet ik van de tabbladnaam (voorbeeld:SS KNMI 9-8 waarbij SS staat voor StralingsSom) als een soort variabele opnemen, of de macro 5 keer kopieren en de tabbladnaam hard opnemen.

Bovendien zal Excel een datum als bv. 9 augustus als 8 september beschouwen. Op de invoer behoor je in de macro dus te controleren en die - indien nodig - aan te passen. Als je nog niet veel ervaring hebt met vba, is dat niet eenvoudig.

Dat heb ik inmiddels ondervonden. Met de eerste optie variabele loop ik nu inderdaad al weer even te klooien
Ga morgen eens kijken of dat is op te lossen met met DATUMWAARDE.

De twee regels die je onderaan toevoegde:

code:

Range("E1").Select
Selection.ClearContents

kun je verkorten tot

code:

.Range("E1").ClearContents

en omdat E1 tot hetzelfde blad behoort als de andere cellen die in de macro worden vermeld, moet die opdracht vóór 'End With' geplaatst worden.

In mijn macro verzuimde ik in de regels 4 en 5 een punt en een 0 toe te voegen. Die regels moeten gewijzigd worden als volgt:

code:

If WorksheetFunction.CountIf(.Columns(1), .Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1), 0)

Ga ik veranderen

[ Voor 10% gewijzigd door Copitano op 10-08-2022 01:31 ]

[dix-neuf]

Copitano schreef op woensdag 10 augustus 2022 @ 00:09:Als je tijdstip 05:30 bedoeld, dan is dat een vast tijdstip. Die kan dus in de macro vast worden opgenomen.

Dan heb je cel E1 niet nodig.

Copitano schreef op woensdag 10 augustus 2022 @ 00:09:Omdat ik de macro elke dag op 5 verschillende tabbladen gebruik moet ik de tabbladnaam (voorbeeld:SS KNMI 9-8 waarbij SS staat voor StralingsSom) als een soort variabele opnemen, of de macro 5 keer kopieren en de tabbladnaam hard opnemen.

Eenzelfde macro voor verschillende bladen moet je nooit meer dan 1 keer in een bestand opnemen. Met bv. een for-nextlus kun je de macro tegelijkertijd in verschillende bladen uitvoeren. Als die bladen opeenvolgend in het bestand staan en niet van plaats wisselen, kun je in plaats van de bladnamen de numerieke volgorde van de bladen gebruiken, dat maakt een for-nextlus erg eenvoudig.

[Copitano]

Thanks👍
Ben even gevloerd door iets wat op Griep lijkt.😱
Zodra ik mij wat beter voel ga ik er weer mee aan de slag😉

[Copitano]

dix-neuf schreef op woensdag 10 augustus 2022 @ 12:10:
[...]
Dan heb je cel E1 niet nodig.

[...]
Eenzelfde macro voor verschillende bladen moet je nooit meer dan 1 keer in een bestand opnemen. Met bv. een for-nextlus kun je de macro tegelijkertijd in verschillende bladen uitvoeren. Als die bladen opeenvolgend in het bestand staan en niet van plaats wisselen, kun je in plaats van de bladnamen de numerieke volgorde van de bladen gebruiken, dat maakt een for-nextlus erg eenvoudig.

Ben vandaag nog eens wat rond gaan googlen en heb dit gevonden. De Sub heb ik zo aangepast en getest. Het werkt

code:

'Sub Verwijder()
Dim m As Integer
'Dim Tijd As Date
Dim Datum As Date

Station = InputBox(Prompt:="Welk Station wil je ophalen?", Title:="Station", Default:="")
Datum1 = InputBox(Prompt:="Welke datum wil je ophalen?", Title:="Datum", Default:="")
'Periode = Station & " " & Datum1
Datum = Datum1 & " " & "05:25"
    If IsDate(Datum) Then
        converteerDatum = FormatDateTime(Day(Datum) & "-" & Month(Datum) & "-" & Year(Datum))
    Else
        converteerDatum = ""
    End If
Range("E1").Value = Datum
With Sheets("SS" & " " & Periode)
If WorksheetFunction.CountIf(.Columns(1), .Range("E1").Value) > 0 Then
m = WorksheetFunction.Match(.Range("E1"), .Columns(1), 0)
.Rows(2 & ":" & m).EntireRow.Delete
End If
    Range("E1").ClearContents
End With

'End Sub

Het kan vast nog wel mooier, dus ga nog wel eens kijken of in de hele module nog wat opgeschoond kan worden. Er zijn wat regels uit geschakeld met een ' omdat de sub deel uitmaakt met een groter macro of elders al een variabelen is ingevuld.

Overigen denk ik dat ik de methode ook in een ander Excel bestand kan gebruiken waar ik een soortgelijk probleempje heb met het opzoeken van een datum in de eerste kolom.

[dix-neuf]

Je brengt best álle cellen onder in 'With .... End With'
en vergeet daarbij de punten niet!

[Copitano]

dix-neuf schreef op vrijdag 12 augustus 2022 @ 09:09:
Je brengt best álle cellen onder in 'With .... End With'
en vergeet daarbij de punten niet!

Ehhh, wat bedoel je precies?
Regel 15 tot onder regel 16?
Ik ben nog niet helemaal thuis in het gebruik van 'With ...... End With' en termen zoals object, eigenschappen etc. en hoop hier weer iets uit op te steken voor een volgend projectje
En welke punt bedoel je?
Voor de regel die verplaatst wordt van 15 tot achter regel 16 en dan die punt er voor?

[ Voor 11% gewijzigd door Copitano op 12-08-2022 15:08 ]

[dix-neuf]

Copitano schreef op vrijdag 12 augustus 2022 @ 14:20:
Ehhh, wat bedoel je precies?
Regel 15 tot onder regel 16?

Ja, cel E1 behoort toch tot hetzelfde blad als de naam die in regel 16 staat?
Als je slechts 1 blad gebruikt en je voert de macro altijd uit vanuit dat ene blad, dan hoef je 'With...End With' niet te gebruiken, omdat er dan voor Excel geen misverstand kan bestaan over het blad waarin de code moet worden uitgevoerd. Maar als je meerdere bladen gebruikt, dan moet je van een cel of bereik in de code aanduiden in welk blad die cel of dat bereik staat, anders neemt Excel aan dat het om de cel of bereik gaat in het blad van waaruit de code wordt uitgevoerd.
Je bent niet verplicht om 'With...End With' te gebruiken, maar dan moet duidelijk zijn om welk blad het gaat.
Eenvoudig voorbeeld, de volgende 2 opdrachten:

code:

Sheets("Blad1").Range("E1").Value = "0:25"
Sheets("Blad1").Range("D10").Formula = "=SUM(D1:D9)"

kun je ook schrijven als:

code:

With Sheets("Blad1")
.Range("E1").Value = "0:25"
.Range("D10").Formula = "=SUM(D1:D9)"
End With

waarbij de punt vóór Range 'Sheets("Blad1")' vervangt.

Copitano schreef op vrijdag 12 augustus 2022 @ 14:20:
Ik ben nog niet helemaal thuis in het gebruik van 'With ...... End With' en termen zoals object, eigenschappen etc.?

'With...End With' en alle andere vba-opdrachten worden zeer goed uitgelegd in de Excel-vba-helpfile en je kunt die ook op vele sites op internet vinden.

[Copitano]

dix-neuf schreef op maandag 15 augustus 2022 @ 14:33:
[...]

Ja, cel E1 behoort toch tot hetzelfde blad als de naam die in regel 16 staat?
Als je slechts 1 blad gebruikt en je voert de macro altijd uit vanuit dat ene blad, dan hoef je 'With...End With' niet te gebruiken, omdat er dan voor Excel geen misverstand kan bestaan over het blad waarin de code moet worden uitgevoerd. Maar als je meerdere bladen gebruikt, dan moet je van een cel of bereik in de code aanduiden in welk blad die cel of dat bereik staat, anders neemt Excel aan dat het om de cel of bereik gaat in het blad van waaruit de code wordt uitgevoerd.
Je bent niet verplicht om 'With...End With' te gebruiken, maar dan moet duidelijk zijn om welk blad het gaat.
Eenvoudig voorbeeld, de volgende 2 opdrachten:

code:

Sheets("Blad1").Range("E1").Value = "0:25"
Sheets("Blad1").Range("D10").Formula = "=SUM(D1:D9)"

kun je ook schrijven als:

code:

With Sheets("Blad1")
.Range("E1").Value = "0:25"
.Range("D10").Formula = "=SUM(D1:D9)"
End With

waarbij de punt vóór Range 'Sheets("Blad1")' vervangt.

[...]
'With...End With' en alle andere vba-opdrachten worden zeer goed uitgelegd in de Excel-vba-helpfile en je kunt die ook op vele sites op internet vinden.

De macro wordt achtereenvolgens per dag uitgevoerd voor vijf werkbladen met verschillende namen. Die werkbladen worden dagelijks toegevoegd aan het werkboek en van een stationsnaam en korte datum voorzien.
Alles werkt nu zoals ik wil.

Dank voor de heldere uitleg. Helemaal duidelijk nu. Weer wat bijgeleerd op m’n ouwe dag