named ranges veranderen van cel ongevraagd en ongemerkt !

[littlepete]

kan iemand mij alstublieft dringend als het kan helpen met een zéér merkwaardig probleem:

in mijn adressenlijst heb ik een kolom moeder en vader die zijn ingevuld bij elke persoon wiens ouders in de lijst voorkomen.
eerst had ik de namen gewoon overgetikt. nu ben ik echter alle voornamen van iemand die ouder is van iemand in de lijst
aan het vervangen door een named range: peter dejaeger wordt dan: vnptrdjg en in de cel vader van zijn kinderen staat dan " = vnptrdjg "

helaas...
naarmate ik verderwerk (vandaag de vierde dag) merk ik om de haverklap dat mensen wiens ouders reeds zijn ingevuld door named ranges,
die namen VERKEERD ZIJN !!! de named range die ik dan in de lijst opzoek staat er nog maar met een verkeerde verwijzing, dikwijls één of twee
rijen hoger of lager !!!

HOE KAN DAT ? ik word hier echt gek van, zo blijf ik bezig met voornamen vervangen door named ranges die er al stonden...
het aanmaken van een named range doe ik door links bovenaan waar ik de celverwijzing zie te klikken en daar de naam van de named range te tikken.

hoe los ik dit op???

hartelijk dank als iemand weet hoe dit op te lossen !!!
peter

 

[GraH]

@GWteB ,
Mag ik je zeggen dat ik dit vrij indrukwekkend vind hie je dit tot in detail hebt kunnen doorgronden. Ik zag het patroon volkomen niet, en stond nog mijlenver van een oplossing aan te bieden.

 

[littlepete]

@GWteB ,
Mag ik je zeggen dat ik dit vrij indrukwekkend vind hie je dit tot in detail hebt kunnen doorgronden. Ik zag het patroon volkomen niet, en stond nog mijlenver van een oplossing aan te bieden.

hallo,
ik ben dan ook wat ik mezelf noem een e-creatieveling... alles wat ik niet ken is een uitdaging, en geloof me, ik kende niets van vba toen ik jaren terug begon aan het aanpassen van mijn drie kolommen adressenlijst, zonder formules en zonder vba... maar zoals men zegt: hoe meer je weet, hoe meer je beseft dat er nog veel meer is dat je niet weet !!! hopelijk kan je dat bestand gebruiken om zelf wat te puzzelen en er gebruik van te maken !!! succes alvast !!!

 

[littlepete]

@GWteB ,
Mag ik je zeggen dat ik dit vrij indrukwekkend vind hie je dit tot in detail hebt kunnen doorgronden. Ik zag het patroon volkomen niet, en stond nog mijlenver van een oplossing aan te bieden.

hallo,
ik ben dan ook wat ik mezelf noem een e-creatieveling... alles wat ik niet ken is een uitdaging, en geloof me, ik kende niets van vba toen ik jaren terug begon aan het aanpassen van mijn drie kolommen adressenlijst, zonder formules en zonder vba... maar zoals men zegt: hoe meer je weet, hoe meer je beseft dat er nog veel meer is dat je niet weet !!! hopelijk kan je dat bestand gebruiken om zelf wat te puzzelen en er gebruik van te maken !!! succes alvast !!!

 

[littlepete]

hallo en : amai !

dat werkt, en dat zou ik dus nooit bedacht kunnen hebben, ik begin zometeen aan het invullen van de juiste verwijsnummers (met formule) in de twee kolommen ! ik begrijp dus dat met named ranges én de functie indirect geen van beiden hadden kunnen werken... hopelijk was het uitdokteren van de oplossing geen werk van uren, maar bottom line:

de oplossing werkt !!!

van harte bedankt dus, ik zou zeggen verwijder alle data en maak van deze lijst je eigen familielijst !!! bedankt !!!
peter, leuven

hallo...
mag ik alsjeblieft je hulp vragen,
de formule werkt perfect. maar om de haverklap merk ik dat de ouders en meter en peter toch weer fout zijn... er is ook één rij die iedere keer weer een fout geeft terwijl de formule voor alle rijen hetzelfde is... het is echt aan het spoken in dat bestand... ik heb nu al blokkeren rijen afgezet, nummers aangepast in uw formule, hij geeft zelfs rijnummers boven de 400 en 500 terwijl daar geen gegevens staan...
wat moet ik denken van een corrupte file? kan dat? waarom dan? wat is je idee? als je wil mailen (heb dat liever) dan via pjmb@telenet.be tenzij het natuurlijk langs hier moet...
al bedankt voor je antwoord, peter.

 

[GWteB]

In uw OneDrive bestand kon ik willekeurig waar dan ook rijen en kolommen invoegen en/of verwijderen, de vader en moeder van de persoon welke oorspronkelijk op rij 7 stond werden blijvend juist weergegeven. Wat ik wel bijzonder opvallend vond was de lange laadtijd van het bestand; ondanks uitgeschakelde macro's circa 1 minuut. Ook het invoegen van rijen of kolommen duurde langer dan men zou verwachten, terwijl Excel dat vermoeden ook al had en bijgevolg vooraf keurig een waarschuwing gaf dat het invoegen wel eens een poosje kon gaan duren. Ik heb hier afgelopen zaterdag verder niet naar gekeken en het bestand na mijn wijzigingen naar WeTransfer geüpload.

De oorzaak van de lange laadtijd was het formaat van uw bestand. Voor vier nauwelijks gevulde werkbladen en wat VBA code is een bestandsgrootte van 219 MB belachelijk veel. Ik heb daar zojuist naar gekeken en mij bleek, dat het gebruikte data bereik van het werkblad "gegevens" zich uitstrekte over 77 kolommen (zoals verwacht) maar over de volle mep aan rijen (dik 1 miljoen). Dit betekent overigens niet meteen dat uw bestand corrupt is, maar dat er "iets" aan de hand is mag duidelijk zijn ...

Met onderstaande code heb ik geprobeerd de visueel ongebruikte ruimte op de werkbladen van uw voorbeeld bestand te claimen als ongebruikt. Dit lukte maar deels.
Weliswaar slonk het bestand met 216 MB (!!) naar 3 MB, maar op het werkblad met de naam "gegevens" bleef het UsedRange object een aantal van 1.048.576 rijen retourneren en Excel's waarschuwing bleef opduiken als er waar dan ook een rij werd ingevoegd. De oorzaak van dit afwijkende werkblad kon ik niet zo 1,2, 3 achterhalen. Ook met uitgeschakelde macro's bleef dit fenomeen zich voordoen. Heb vervolgens handmatig het gebruikte bereik (91 rijen over 77 kolommen) gekopieerd naar een leeg werkblad en het oude werkblad gewist. Het mysterieuze fenomeen was daarmee verdwenen, evenals de opmaak van kolombreedtes en rijhoogtes, maar goed, da's te overzien lijkt me ...

Al met al denk ik dan ook, dat het verstandig is om de data van uw adressenlijst naar een leeg werkblad te kopiëren en van daar af weer verder te bouwen. Wellicht is daarmee dan tevens uw telkens terugkerende klacht verdwenen.

Tot slot nog het volgende. Om het eenvoudig te houden kunt u de inhoud van mijn vorige post vergeten. De functies INDIRECT(), RIJ() en KOLOMLETTERS() zijn geheel overbodig MITS u de cel opmaak instelt op Standaard. Als u geen getallen bestaande uit 15 cijfers of meer gebruikt die ook als zodanig op het scherm zichtbaar moeten zijn, is het overbodig om cellen op te maken als Tekst. Zoals u al eerder gemerkt heeft resulteert een als Tekst opgemaakte cel erin, dat een ingevoerde formule niet als formule wordt geaccepteerd. Met een formule zoals bijvoorbeeld

=N91 & " " & O91

in kolom BR worden voornaam en achternaam van (een) moeder opgehaald en - gescheiden door een spatie - in de bewuste cel van kolom BR weergegeven. In de cel van kolom F kan dan een verwijzing naar de cel in kolom BR worden opgenomen, zoals ook voorgesteld in mijn post #9.

Public Sub DeleteUnusedRange()

    Dim lLastRow As Long, lLastColumn As Long
    Dim lRealLastRow As Long, lRealLastColumn As Long
    Dim Sht As Worksheet, CurSht As Worksheet
   
    Application.ScreenUpdating = False
    Set CurSht = ActiveSheet
    ThisWorkbook.Activate
   
    For Each Sht In ThisWorkbook.Worksheets
        With Sht
            If .AutoFilterMode Then
                .AutoFilterMode = False
            End If

            With .Range("A1").SpecialCells(xlCellTypeLastCell)
                lLastRow = .Row
                lLastColumn = .Column
            End With

            lRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
            lRealLastColumn = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

            If lRealLastRow < lLastRow Then
                .Range(.Cells(lRealLastRow + 1, 1), .Cells(lLastRow, 1)).EntireRow.Delete
            End If
   
            If lRealLastColumn < lLastColumn Then
                .Range(.Cells(1, lRealLastColumn + 1), .Cells(1, lLastColumn)).EntireColumn.Delete
            End If
            .Activate
            ActiveSheet.UsedRange
        End With
    Next Sht
    ThisWorkbook.Save
   
    CurSht.Parent.Activate
    CurSht.Activate
    Application.ScreenUpdating = True
End Sub

 

[littlepete]

In uw OneDrive bestand kon ik willekeurig waar dan ook rijen en kolommen invoegen en/of verwijderen, de vader en moeder van de persoon welke oorspronkelijk op rij 7 stond werden blijvend juist weergegeven. Wat ik wel bijzonder opvallend vond was de lange laadtijd van het bestand; ondanks uitgeschakelde macro's circa 1 minuut. Ook het invoegen van rijen of kolommen duurde langer dan men zou verwachten, terwijl Excel dat vermoeden ook al had en bijgevolg vooraf keurig een waarschuwing gaf dat het invoegen wel eens een poosje kon gaan duren. Ik heb hier afgelopen zaterdag verder niet naar gekeken en het bestand na mijn wijzigingen naar WeTransfer geüpload.

De oorzaak van de lange laadtijd was het formaat van uw bestand. Voor vier nauwelijks gevulde werkbladen en wat VBA code is een bestandsgrootte van 219 MB belachelijk veel. Ik heb daar zojuist naar gekeken en mij bleek, dat het gebruikte data bereik van het werkblad "gegevens" zich uitstrekte over 77 kolommen (zoals verwacht) maar over de volle mep aan rijen (dik 1 miljoen). Dit betekent overigens niet meteen dat uw bestand corrupt is, maar dat er "iets" aan de hand is mag duidelijk zijn ...

Met onderstaande code heb ik geprobeerd de visueel ongebruikte ruimte op de werkbladen van uw voorbeeld bestand te claimen als ongebruikt. Dit lukte maar deels.
Weliswaar slonk het bestand met 216 MB (!!) naar 3 MB, maar op het werkblad met de naam "gegevens" bleef het UsedRange object een aantal van 1.048.576 rijen retourneren en Excel's waarschuwing bleef opduiken als er waar dan ook een rij werd ingevoegd. De oorzaak van dit afwijkende werkblad kon ik niet zo 1,2, 3 achterhalen. Ook met uitgeschakelde macro's bleef dit fenomeen zich voordoen. Heb vervolgens handmatig het gebruikte bereik (91 rijen over 77 kolommen) gekopieerd naar een leeg werkblad en het oude werkblad gewist. Het mysterieuze fenomeen was daarmee verdwenen, evenals de opmaak van kolombreedtes en rijhoogtes, maar goed, da's te overzien lijkt me ...

Al met al denk ik dan ook, dat het verstandig is om de data van uw adressenlijst naar een leeg werkblad te kopiëren en van daar af weer verder te bouwen. Wellicht is daarmee dan tevens uw telkens terugkerende klacht verdwenen.

Tot slot nog het volgende. Om het eenvoudig te houden kunt u de inhoud van mijn vorige post vergeten. De functies INDIRECT(), RIJ() en KOLOMLETTERS() zijn geheel overbodig MITS u de cel opmaak instelt op Standaard. Als u geen getallen bestaande uit 15 cijfers of meer gebruikt die ook als zodanig op het scherm zichtbaar moeten zijn, is het overbodig om cellen op te maken als Tekst. Zoals u al eerder gemerkt heeft resulteert een als Tekst opgemaakte cel erin, dat een ingevoerde formule niet als formule wordt geaccepteerd. Met een formule zoals bijvoorbeeld

=N91 & " " & O91

in kolom BR worden voornaam en achternaam van (een) moeder opgehaald en - gescheiden door een spatie - in de bewuste cel van kolom BR weergegeven. In de cel van kolom F kan dan een verwijzing naar de cel in kolom BR worden opgenomen, zoals ook voorgesteld in mijn post #9.

Public Sub DeleteUnusedRange()

    Dim lLastRow As Long, lLastColumn As Long
    Dim lRealLastRow As Long, lRealLastColumn As Long
    Dim Sht As Worksheet, CurSht As Worksheet
  
    Application.ScreenUpdating = False
    Set CurSht = ActiveSheet
    ThisWorkbook.Activate
  
    For Each Sht In ThisWorkbook.Worksheets
        With Sht
            If .AutoFilterMode Then
                .AutoFilterMode = False
            End If

            With .Range("A1").SpecialCells(xlCellTypeLastCell)
                lLastRow = .Row
                lLastColumn = .Column
            End With

            lRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
            lRealLastColumn = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

            If lRealLastRow < lLastRow Then
                .Range(.Cells(lRealLastRow + 1, 1), .Cells(lLastRow, 1)).EntireRow.Delete
            End If
  
            If lRealLastColumn < lLastColumn Then
                .Range(.Cells(1, lRealLastColumn + 1), .Cells(1, lLastColumn)).EntireColumn.Delete
            End If
            .Activate
            ActiveSheet.UsedRange
        End With
    Next Sht
    ThisWorkbook.Save
  
    CurSht.Parent.Activate
    CurSht.Activate
    Application.ScreenUpdating = True
End Sub

ik heb het doorgaan tot de miljoenste rij al meermaals vastgesteld... een deel van het probleem ligt misschien bij onedrive de cloud, ik heb ondertussen versie 32 opgeslagen op mijn harde schijf, ver weg van mijn cloud... ik ga het wel eens proberen dat overzetten van mijn gegevens naar een nieuw bestand, maar dat is wel een pak werk aan indelen en zo maar het is een uitdaging !!!
nog eens dank voor alle hulp, ik doe voorlopig andere dingen in mijn lijst ik laat de mama en papa effe rusten...

 

[littlepete]

ik heb ondertussen alle cellen in mijn lijst gekopieerd naar een nieuw leeg bestand, zie mijn verwondering bij het vaststellen van de grootte van het bestand: boven de 4000 kB eerst, en nu nog 1470 kB !!! ik wil u dus van harte danken voor alle hulp... ik heb het uitermate geapprecieerd !!!

 

[GWteB]

Graag gedaan en bedankt voor de terugkoppeling!