Offset

[Gurtie]

Beste lezers,

ik heb een stukje code dat een bepaald bereik kopieert van het ene naar het andere blad. Echter, als ik deze gegevens heb gekopieerd en ik sorteer dit op een andere manier, dan verandert Excel de formules zodat ze naar de oorspronkelijke broncel blijven verwijzen. Dit is niet de bedoeling!
Normaal gesproken los ik dit op met OFFSET, maar het lukt me niet om deze functie te combineren met onderstaande code.

Range("H7").Select
    ActiveCell.FormulaR1C1 = "=Formules!RC[209]"
    Range("H7").Select
    Selection.autofill Destination:=Range("H7:H220"), Type:=xlFillDefault

Iemand de oplossing? Het zal vast makkelijk zijn, maar het lukt me niet

groeten, Gurtie

edit: een excelbestandje bijhangen is in dit geval niet zo handig, aangezien het een groot bestand is; als het echter noodzakelijk is, dan kan ik dit op verzoek doen

 

[Wigi]

Als die formules niet mogen wijzigen, neem die cellen dan niet mee op in de sortering...

 

[Wigi]

Dit is trouwens beter dan vorige code:

Range("H7:H220").Formula = "=Formules!RC[209]"

 

[Gurtie]

De formules staan niet standaard in de cel. Als deze code wordt aangeroepen, worden de bestaande formules overruled door bovenstaande. Vandaar dat het op deze manier moet..

 

[Wigi]

Programmeer het sorteren dan ook in de code, en overschrijf de formules pas na het sorteren.

 

[Gurtie]

Tsjaaaaaaaaa maar de truc is dat er op meerdere manieren gesorteerd kan worden en dat dit niet per definitie gebeurt bij het aanroepen van de code, maar wanneer de gebruiker dat zelf wil

 

[Wigi]

En is er een mogelijkheid om de code te laten runnen na het sorteren?

 

[Gurtie]

Nee, want niet in alle gevallen zal de code uberhaupt aangeroepen worden. Ik dacht dat het een simpele vraag van me was, maar zo makkelijk is t dus niet om OFFSET in de code te verwerken? Anders maar de sorteerfuncties verwijderen als t niet anders kan, want nu gaan mn formules de vernieling in en dat werkt door in het hele bestand, een nogal complex gebeuren

 

[Wigi]

En zeg dan eens wat je verstaat onder OFFSET.

 

[Gurtie]

Een verwijzing naar een specifiek bereik. Bijvoorbeeld de formule:
=(OFFSET(A15,0,0) verwijst altijd naar cel A15, ook als de data uit cel A15 vanwege sorteermogelijkheden verdwijnt naar een andere cel.

 

[Wigi]

Sub wigi()
    Range("H7:H220").Formula = "=OFFSET(Formules!RC[209],,)"
End Sub

Wigi

 

[Gurtie]

Ah, deze doet inderdaad wat ik wil, maar ik zie nu dat het niet werkt zoals ik verwachtte Bedankt voor je tijd weer wigi, ik ga me beraden op het vervolg.

 

[Ginger]

### sorry, Topic is al gesloten, maar ik dacht dat dit toch nog een interessante aanvulling voor de TS zou zijn... ###


Gurtie, Je wilt dat een cel ALTIJD naar dezelde verwezen cel blijft kijken? Dat doe je met een absolute verwijzing.
Als je de vierkante haken om je '209' weg haalt, zie je dat het dollarteken bij de verwijzing komt.

Probeer dit voorbeeldje maar 'ns in een blanco sheet

Sub test()
ActiveCell.FormulaR1C1 = "=R" & ActiveCell.Row & "C209"
End Sub

Dan zal je zien dat zowel de rij als de kolom absoluut gemaakt zijn. Je kan nu sorteren wat je wilt, maar de verwijzing blijft altijd in stand!

Groet, Leo

 

[Gurtie]

Bedankt Leo, maar dat is niet het probleem.
Bijvoorbeeld worksheet A cel A4 moet ALTIJD verwijzen naar bijv. worksheet B cel A4.
Als worksheet A gesorteerd wordt, moet cel A4 nog steeds verwijzen naar worksheet B cel A4. En dit geldt ook voor 5, 6, etc.

 

[Ginger]

Gurtie, Ah op zo'n manier.... Ga dan 'ns spelen met deze formule (engels):

=INDIRECT(ADDRESS(ROW();1))

en nederlands:

=INDIRECT(ADRES(RIJ();1))

De '1' staat voor de kolom waarnaar gekeken moet worden. Aan jou de eer om daar de juiste kolom op te geven.

Als je deze formule opneemt in je code en dus daar plaatst waar je 'm wilt hebben, blijft die cel altijd naar hetzelfde kijken!

Groet, Leo

 

[Gurtie]

Indirect....die functie heb ik niet eerder gebruikt. Het lijkt op Offset of ben ik gek? Nou, ik ga er mee aan de slag, bedankt!:thumb: