Tabelmatrix verwijzen naar cel --> VBA code

[drakie]

Halle allemaal,

Ik zit met het volgende probleem wat ik probeer op te lossen.
Ik heb een aantal sheets die met elkaar in verbinding staan maar omdat de adressen van de sheets nog wel eens wijzigt (van D:/DATA naar bijvoorbeeld F:/Systeem) werken mijn verwijzingen niet meer.

Sheet 02.02.xlsm zoekt informatie op uit een bron document bron.xlsm welke één map hoger staat
(02.02.xlsm staat in D:/Data/Project/Indicator)
(bron.xlsm staat in D:/Data/Project

Wanneer ik nu in cel A1: 02.02 invul moet in cel B1 en C1 de bijbehorende waarden worden gevonden die worden opgezocht in het bron.xlsm bestand

Hiervoor heb ik gebruik gemaakt van de verticaal zoeken formule: =VERT.ZOEKEN(B12;{Tabelmatrix};7;0).
Echter omdat de excel mappen wel eens als geheel worden verplaatst (mappenstructuur blijft in stand) wijzigt de verwijzing.
Deze adresverwijzing laat ik in cel D1 aanroepen door de formule: =CEL("bestandsnaam").
Nu wil ik dus dat het resultaat wat in deze cel wordt gegeven als "Tabelmatrix" wordt ingevoerd in de formule =VERT.ZOEKEN(A1;{Tabelmatrix};7;0) = =VERT.ZOEKEN(A1;D1;7;0)
Excel begrijpt dit echter niet en ik krijg steeds een foutmelding.

Ik heb al gezocht op internet maar kan geen antwoord op mijn vraag vinden. Kan iemand mij verder helpen (mag ook een hele andere insteek zijn hoor als het resultaat maar hetzelfde is)
Essentie van het probleem is dus dat in cel A1 een waarde ingevuld wordt waarbij de bijbehorende waarde in een bron document welke een map hoger staat opgezocht moeten worden waarbij het resultaat in B1 en C1 wordt getoond.
Als de oplossing in VBA ligt hoor ik dat ook graag en dan hoop ik natuurlijk ook dat iemand de VBA code hiervoor weet.

Alvast bedankt voor jullie hulp!

 

[Niels28]

Uitleg is lastig te begrijpen, maar ik ga ervanuit dat je een benoemd bereik hebt met de naam tabelmatrix.
Hier kun je bv bij het openen het bereik van veranderen.
Plaats macro achter Thisworkbook

Private Sub Workbook_Open()
SpathNieuw = Split(ThisWorkbook.Path, "Indicator")
SpathOud = Split(ThisWorkbook.Names("tabelmatrix").RefersTo, "[")
ThisWorkbook.Names("tabelmatrix").RefersTo = Replace(ThisWorkbook.Names("tabelmatrix").RefersTo, SpathOud(0), "='" & SpathNieuw(0))
End Sub

Niels

 

[snb]

of

Sub tst()
    For Each c01 In ThisWorkbook.LinkSources
        c01 = Replace(CreateObject("scripting.filesystemobject").GetFile(ThisWorkbook.FullName).ParentFolder.ParentFolder & "\" & Dir(c01), "\\", "\")
    Next
End Sub

 

[drakie]

Ik heb de codes geprobeerd maar nog niet het gewenste resultaat bereikt. Ik zal het probleem nog een keer proberen toe te lichten:

Als je de volgende formule in excel gebruikt in cel B1 (waar het resultaat moet komen te staan) is deze opgebouwd uit een aantal onderdelen:
=VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal; [benaderen]

- Mijn zoekwaarde staat in cel A1
- De tabelmatrix is een verwijzing naar een tabel in een ander excelbestand welke op een andere locatie staat. Deze verwijzing bestaat dus uit een linkverwijzing en tabel. Bijv: D:/Data/Project.
- kolomindex_getal is welke bijbehorende waarde er getoond moet worden
- [benaderen] is leeg

De formule werkt alleen als deze er zo uit ziet:
=VERT.ZOEKEN(A1;'D:\Data\Project\[KPI_overleggen.xlsm]KPI'!$A$7:$O$302;7);7)

Omdat de gehele directory wel eens verplaatst wordt naar een andere map of schijf (netwerkschijf) werkt de verwijzing naar 'D:\Data\Project\[KPI_overleggen.xlsm]KPI'!$A$7:$O$302 niet meer. Daarom zou het mooi zijn als ik deze verwijzing in een cel D1 kan invullen (laten opzoeken door excel).
De formule komt er dan dus zo uit te zien:
=VERT.ZOEKEN(A1;D1;7);7)
waarbij dus in D1: 'D:\Data\Project\[KPI_overleggen.xlsm]KPI'!$A$7:$O$302 komt te staan (deze waarde wordt automatisch opgezocht door excel en dat werkt reeds)

De formule werkt echter op deze manier niet en ik zoek een manier dat dit wel werkt. Nu werk ik met de functie INDIRECT() maar daarvoor moet altijd het bron.xlsm document open zijn wat niet gewenst is.

Ik hoop dat dit één en ander duidelijker maakt.

 

[Niels28]

Maak een benoemdbereik aan met de naam "tabelmatrix" en als verwijzing 'D:\Data\Project\[KPI_overleggen.xlsm]KPI'!$A$7:$O$302.
Dan werken de macro's wel.

Niels

 

[sylvester-ponte]

hallo Niels

ik probeer als oplossing een function te maken, die jouw "tabelmatrix" in worksheetfunctions.vlookup gebruikt .
maar als ik gedefinieerde naam"tabelmatrix" invoeg in de vlookup gaat het fout.
dit gaat fout:

Function VertikaalZoekAnders(Zoekwaarde, Tabelmatrix, Kolomindex_getal, Benaderen)
   
    VertikaalZoekAnders = WorksheetFunction.VLookup(Zoekwaarde, Tabelmatrix, Kolomindex_getal, Benaderen)

End Function

heb jij eenig idee waarom het niet werkt?
groet sylvester

 

[Niels28]

Als je een benoemd bereik in vba gebruikt geef je die aan als:

range("tabelmatrix")

Niels

 

[HSV]

hallo Niels

heb jij eenig idee waarom het niet werkt?
groet sylvester

Werkt bij mij goed Sylvester.
Alleen niet getest op een verwijzing naar een ander bestand.
Misschien zit daar de fout.

 

[Niels28]

Heb functie van Sylvester ook getest en bij mij werkt het ook gewoon.
Ook met verwijzing naar ander bestand.

Niels

 

[sylvester-ponte]

wat heb jij ingevuld bij "tabelmatrix"
kun je je voorbeeld posten?

 

[Niels28]

@ Sylvester

Hierbij een rar-bestandje met daarin het bronbestand en een map Indicator met het doel bestand.
Als je deze ergens op jouw computer zet moet de verwijzing automatisch veranderen.
In het doelbestand staat jouw functie

Bekijk bijlage Documents.rar



Niels

 

[sylvester-ponte]

Niels, ik heb geen rar
kun je het zippen

groet sylvester

 

[HSV]

Kun je bijna overal gratis downloaden Sylvester.

 

[sylvester-ponte]

Harry en Niels bedankt ik heb rar gedownload en het werkt
met deze function kan ik Drakie misschien een eenvoudige opl geven met vba

 

[huijb]

@sylvester-ponte Je vertoeft al sinds 2007 op Helpmij. Lijkt me dat je nu wel weet dat onnodig quoten niet is toegestaan. Onnodig quotes verwijderd.

 

[drakie]

Allemaal bedankt! Het werkt inmiddels en het maakt nu niet meer uit waar de mappen staan op de computer of op het netwerk.
Hartstikke bedankt!

 

[drakie]

Helaas toch nog niet helemaal opgelost.

Als ik de documenten van Niels28 download dan werkt alles.
Zodra ik deze dan iets ga aanpassen naar de situatie zoals ik die op mijn computer heb gaat die foutmeldingen geven.

Wat er dan gebeurd is dat als het bron.xlsm bestand open is en ik open het doel.xlsm bestand die de volgende foutmelding geeft:

Fout 1004 tijdens uitvoering
U hebt een ongeldige naam opgegeven

Mogelijk redenen zijn onder meer:
-de naam begint niet met een letter of onderstrepingsteken
etc

Foutmelding op:

ThisWorkbook.Names("tabelmatrix").RefersTo = Replace(ThisWorkbook.Names("tabelmatrix").RefersTo, SpathOud(0), "='" & SpathNieuw(0))

Iemand een idee?
Enige wat ik nu had aangepast is de map van Indicator naar Sheet_indicator. Dit heb ik ook in de code aangepast:

Private Sub Workbook_Open()
SpathNieuw = Split(ThisWorkbook.Path, "Sheet_indicator")
SpathOud = Split(ThisWorkbook.Names("tabelmatrix").RefersTo, "[")

ThisWorkbook.Names("tabelmatrix").RefersTo = Replace(ThisWorkbook.Names("tabelmatrix").RefersTo, SpathOud(0), "='" & SpathNieuw(0))
End Sub

 

[Niels28]

Heet de map precies het zelfde incl. hoofdletters?

Niels

 

[drakie]

Helemaal precies: Sheet_indicator

de code werkt nu alleen als ik eerst het doel.xlsm bestand open en vervolgens pas het bron.xlsm bestand.
echter moet het ook werken (zonder foutmeldingen) als het bron.xlsm bestand open is.

 

[Niels28]

Plaats even een voorbeeldje net zoals ik dat eerder heb gedaan.

Niels