Hoe vermijd ik ongewenst omvormen van Links tijdens Copy?

[MDN111]

Hallo !

Het lukt mij niet om een oplossing te vinden voor het ongewenst omvormen van verwijzingen bij het kopiëren van een Range. Voor de duidelijkheid heb ik de oorspronkelijke code zodanig vereenvoudigd dat alleen de essentie overblijft.

We hebben twee geopende Excelbestanden: Book1.xlsm en Book2.xlsm.

In Book1.xlsm zijn er twee werkbladen: Sheet1 en Sheet2.
In Sheet1 staat een Link naar Cel A1 van Sheet2. De formule daarvan is =Sheet2!A1

In Book2.xlsm zijn er eveneens twee werkbladen: Sheet1 en Sheet2

We kopiëren nu de cel A1 van Sheet1 van Book1 naar de Sheet1 van Book2 met deze code:
Workbooks("Book1.xlsm").Sheets("Sheet1").Range("A1").Copy Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A1")

In de cel A1 van Sheet1 van Book2 verwachtte ik als formule ook =Sheet2!A1 maar dat blijkt niet het geval want er staat =[Book1.xlsm]Sheet2!A1 wat uiteraard een foutief resultaat oplevert!

De vraag is: Kunnen we dergelijke ongewenste omvorming van die formule vermijden?

Ik weet dat men dat achteraf kan herstellen door

Workbooks("Book2.xlsm").ChangeLink Name:="Book1.xlsm", NewName:="Book2.xlsm", Type:=xlExcelLinks

maar die oplossing kan ik in de oorspronkelijke code niet toepassen omdat er ongeldige verwijzingen inzitten. Die heeft de gebruiker erin gezet en ik heb niet het recht om daaraan iets te doen.

Even dacht ik het gevonden te hebben door gewoonweg de formule te kopiëren met onderstaande code (die nu wel uit het origineel komt):

For Each myCell In .Range(myRangeAsString).Cells
         If myCell.HasFormula Then
             If InStr(myCell.Formula, "totaal overzicht") = 0 Then
                 myCell.Formula = oSheetToClean.Range(myCell.Address).Formula   'Hier loopt het soms fout. Run-time error.
             End If
         End If
     Next

Bij de uitvoering van deze code stuit ik soms op een Run-time error, zie bijlage, a.u.b.
Het vervelende is dat deze fout soms wel optreedt en soms niet. Blijkbaar op willekeurige momenten. Ik heb ook al getest met EnableEvents op False en
Calculation op xlManual, maar niets van dat bracht enige zoden aan de dijk.

Heeft misschien iemand een idee?

Bij voorbaat dank,

Grtz,
MDN111.

 

[MDN111]

Dag cow18 !

Bedankt voor uw antwoord.

Inderdaad, daar had ik op dat moment niet aangedacht. Ik heb dat getest en de macro draait nu volledig tot het einde, wat te verwachten was.

Er blijven echter nog twee vragen onbeantwoord:

1. Telkens een wijziging van de formule mislukt zien we geen foutmelding meer, maar dat wil ook zeggen dat de formule in kwestie niet gewijzigd wordt. Hoeveel er zo zijn, is moeilijk in te schatten. En met zo'n 2000 formules is een handmatige controle ook niet mogelijk. Ik moet dus iets schrijven om die ongeldige referenties op te sporen. Dat lijkt mij geen probleem want er is voldoende info op het www, zoals hier bijvoorbeeld. Dit probleem kan ik wel oplossen zonder hulp (voorlopig toch )
   
   
2. Waar ik wel een probleem mee heb, is weer een vreemde gedraging. Door de formule rechstreeks te wijzigen zoals in de For-Next-Loop wordt die formule inderdaad correct gewijzigd, maar het resultaat op het werkblad geeft #REF!. Als ik op de formulebalk klik om de cel "openen" om te bewerken en zonder iets te wijzigen op het groene vinkje klik, dan verschijnt in de cel het correcte resultaat (???). Hebt u misschien een idee over wat de oorzaak hiervan kan zijn?

Grtz,
MDN111.

 

[VenA]

Ik zou eens beginnen met de samengevoegde cellen eruit te gooien.

 

[MDN111]

Dag VenA,

Bedankt voor je antwoord.

Ja, ik weet het... Samengevoegde cellen maken het leven er niet eenvoudiger op, maar de bestanden in kwestie zijn niet de mijne, dus leer ik er maar mee omgaan. Ik heb het er al met de gebruiker over gehad, en ik kan hem wel naar het water leiden maar niet dwingen om te drinken.

Een vraag die onmiddellijk opduikt bij mij is wel: Denkt u dat het ongewenst gedrag en de foutmeldingen veroorzaakt (kunnen) worden door die samengevoegde cellen?

Grtz,
MDN111.

 

[VenA]

Ik denk het wel. Maar kan je natuurlijk zelf even proberen aangezien je graag met plaatsjes in een Excelforum werkt.

Sub test()
  Range("A4").ClearContents
End Sub

Zal een foutmelding geven.

 

[MDN111]

Ja, het verbaast me niet dat het een foutmelding oplevert. Ik ga deze topic op "opgelost" zetten en de gebruiker verzoeken z'n formules te verbeteren. Als de formules vrij zijn van referenties naar niet bestaande worksheets, dan werkt de oorspronkelijke macro (met Workbook.Changelink) wel.

:thumb:
Grtz,
MDN111.