Juiste sheet in Excel wordt niet gevonden Via 'Sheets'

[Plotinus]

L.S.

Ik voer een paar eenvoudige acties uit op verschillende sheets. Onderstaande actie is er een van. Het gekke is nu dat als ik niet in de betreffende sheet sta, de actie niet wordt uitgevoerd, of niet tot resultaat komt (naam klopt zeker). Dit is wel het geval als ik in de sheet sta. Dit verbaast mij ten zeerste aangezien met 'with sheets(Samenvatting")' expliciet naar de betreffende sheet wordt verwezen. Naar mijn gevoel zou ik er dus niet in hoeven moeten staan. Iemand een idee hoe dat kan?

 Dim rBereik As Range
With Sheets("Samenvatting")
        For Each rBereik In Range("A4:P" & AantalRijen)
            If IsError(rBereik) Then rBereik = ""
        Next
    End With

Iets gelijkaardigs treedt op met onderstaande code:

   AantalRijen = Sheets("LoggedSpectra").Range("P" & Rows.Count).End(xlUp).Row + 1

Als ik in een grafiek sta en ik roep de macro aan, dan krijg ik de foutmelding 'Methode Rows van object_Global is mislukt'. Sta ik echter in willekeurig welke sheet ook, dan gaat dit wel goed. Lijk niet helemaal logisch gezien het bovenstaande???

gegroet,
mrt

 

[Mark xl]

Yep, het is een klassieke beginnersfout.
Je zult hem uiteraard nog eens maken(ik vergeet het ook nog wel eens ), maar dan weet je waar de fout zit.

Het gebruiken van een With statement wil nog niet zeggen dat automatisch de Range objecten onder het object van het With statement vallen, dat zou ook niet moeten want anders zou je binnen een With statement alleen maar object gerelateerde acties kunnen uitvoeren.

Je zou moeten gebruiken : (let op de punt)

 Dim rBereik As Range
With Sheets("Samenvatting")
        For Each rBereik In [COLOR="red"].[/COLOR]Range("A4:P" & AantalRijen)
            If IsError(rBereik) Then rBereik = ""
        Next
End With

daarmee geef je aan dat het range object Expliciet deel uit maakt van het blad "Samenvatting". geef je dat niet aan dan wordt het huidige sheet gebruikt.

en aangezien de with statement hier niet veel toevoeging heeft kun je nog beter

For Each rBereik In Sheets("Samenvatting").Range("A4:P" & AantalRijen)
    If IsError(rBereik) Then rBereik = ""
Next

Range("A4: P" & AantalRijen) (zonder punt in de with statement) wordt geëvalueerd als Activesheet.Range("A4: P" & AantalRijen)
Bovendien moet dan het Actieve sheet van het Type Excel.Worksheet Zijn, anders treedt een fout op (een chart is ook een type sheet, en deze heeft geen range objecten onder zich.)

Kortom:
Je moet voor elk Range Object Expliciet het Worksheet Object benoemen.
Doe je dat niet, dan kan je code onstabiel worden of onvoorspelbare/verkeerde informatie tonen

Hetzelfde geldt overigens voor je rows.count
de vraag is [WAT].rows.count. Een Chart heeft geen rows, dus dat resulteert wederom in een fout bij het oproepen van een macro terwijl je niet in een worksheet bezig bent.

Ik hoop dat je het begrijpt, anders leg ik het graag nog wat duidelijker uit, mbv een voorbeeldje.

Groeten,
Mark.

 

[Plotinus]

Tof Mark dat je er zo uitgebreid opin bent gegaan. Je oplossing werkte ook in één keer. Ik heb alleen nog geen oplossing voor 'Rows'. De gehele code ziet er als volgt uit.:

Sub macro3()
    Application.ScreenUpdating = False
    Dim AantalRijen As Long, c As Range
    Dim rBereik As Range
    AantalRijen = Sheets("LoggedSpectra").Range("P" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Tonaal")
        .Range("A4:BO4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:BO" & AantalRijen).FillDown
    End With
      With Sheets("Laagfrequent")
        .Range("A4:AA4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:AA" & AantalRijen).FillDown
    End With
     With Sheets("Samenvatting")
        .Range("A4:AA4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:P" & AantalRijen).FillDown
    End With
    Calculate
    Application.ScreenUpdating = False
    For Each rBereik In Sheets("Samenvatting").Range("A4:P" & AantalRijen)
        If IsError(rBereik) Then rBereik = ""
    Next
    Application.ScreenUpdating = True
End Sub

Op zich niet zo spannend: na import van nieuwe meetdata moet op de eveluatiesheets de oude data worden verwijderd en worden afgestemd op de nieuw aangeboden data, dus wissen sheet en van boven af aan de zaak weer opbouwen. Maar zoals gezegd, als ik in een grafiek sta, dan loopt ie vast op 'Rows'. Heb je een suggestie hoe ik dat kan elimineren/vermijden, zodat het altijd werkt, ongeacht waar ik sta?

Wat ook een klein beetje niet zo fraai is, dat als ik de macro oproep via het menu en dan op uitvoeren klik, een deel van het dialoogvenster (waar alle macro's genoemd staan) op het scherm blijft staan, totdat de hele klus is afgerond. Niet echt fraai. Is dat gemakkelijk te helpen?

gegroet,
mrt

 

[Mark xl]

Jazeker kan ik dat.

Je hebt nog steeds niet aangegevenvan welk werkblad je Rows je wilt tellen
De vraagt blijft dus [WAT].Rows.Count
Rows.count is in de context van jouw macro ook Een objectvan het type Worksheet.Rows

Dus Je eerste regel zou moeten zijn:

AantalRijen = Sheets("LoggedSpectra").Range("P" &  [COLOR="red"][B]Sheets("LoggedSpectra").[/B][/COLOR]Rows.Count).End(xlUp).Row + 1

Of, wellicht overzichtelijker, wel weer met een With statement, en dan weer goed op de punt letten!

With Sheets("LoggedSpectra")
    AantalRijen = [COLOR="red"][B].[/B][/COLOR]Range("P" & [COLOR="red"][B].[/B][/COLOR]Rows.Count).End(xlUp).Row
End With

Betreft probleem 2:
Ik denk dat je misschien de eerste regel "Application.screenupdating = False" kunt verwijderen
Ik denk dat daardoor het scherm macro's blijft "hangen".

Je doet namelijk verder geen "flashy" bewerkingen(o.a. het actieve blad opmaken, cel voor cel vullen, ...) voor je de For..Next loop start.

das overigens ook nog een leuke,. je kunt je hele For..Next loop door onderstaande, meer efficiënte vervangen.
(En dan kun je net zo goed je hele screenupdating weghalen)

    With Sheets("Samenvatting").Range("A4:P" & AantalRijen)
        On Error Resume Next
        'ik weet niet of het formules of waarden zijn dus dit zijn ze allebei
        .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
        .SpecialCells(xlCellTypeConstants, xlErrors).ClearContents
    End With

Je macro zou dan zo moeten werken: (typefouten voorbehouden )

Sub Macro3()
    Dim AantalRijen As Long, c As Range
    Dim rBereik As Range
    
    With Sheets("LoggedSpectra")
        AantalRijen = .Range("P" & .Rows.Count).End(xlUp).Row
    End With
    
    With Sheets("Tonaal")
        .Range("A4:BO4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:BO" & AantalRijen).FillDown
    End With
    
    With Sheets("Laagfrequent")
        .Range("A4:AA4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:AA" & AantalRijen).FillDown
    End With
    
    With Sheets("Samenvatting")
        .Range("A4:AA4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents
        .Range("A3:P" & AantalRijen).FillDown
    End With
    
    Calculate
    'fouten weghalen
    With Sheets("Samenvatting").Range("A4:P" & AantalRijen)
        On Error Resume Next
        'ik weet niet of het formules of waarden zijn dus dit zijn ze allebei
        .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
        .SpecialCells(xlCellTypeConstants, xlErrors).ClearContents
    End With
    On Error GoTo 0

End Sub

 

[Plotinus]

uit de kunst; het werkt nu helemaal zoals ik het voor ogen had (en slechts een foutje in de code moeten aanpassen); mijn dank is groot.

gegroet,
mrt