Vlookup in VBA naar andere sheet in zelfde bestand werkt niet

[Jand66]

Het excel bestand heeft 3 sheets, Plaatsingen, Gestarte_Trajecten, Regulier. Op Regulier is button gedefinieerd waarmee macro wordt gestart.
In de macro zijn ondere de volgende variabelen gedefinieerd

 Dim ZoekResultaat As Variant
Dim ClientDs As Range

In de initialisatie sub wordt ClientDs geinitialiseerd met de waarde uit kolom A van Gestarte_Trajecten

Set ClientDs = Range(Cells(CurRow, 1), Cells(CurRow, 1))           'Kolom ClientDs

Een onderdeel van de macro is dat er moet worden gekeken of de waarde van ClientDs ook voorkomt in kolom A van Plaatsingen. Dit wil ik doen met de functie VLookup. Ik heb al van alles geprobeerd en al de wereld aan meldingen gehad. De laatste stand is als volgt:

 'Lopende trajecten zonder plaatsing
  If TrajectResultaat = "" Then
    ZoekResultaat = ""
    Err.Clear
    ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs, Range("Plaatsingen!A2:Q131"), 1, False)
    If Err.Number = 2042 Then              
      Wrk_LopendTrajectZonderPlaatsing = 1
    End If
  End If

Bovenstaande code levert Error 1004 op. Error text is "Eigenschap VLookup van klasse WorksheetFunction kan niet worden opgehaald."

Ik heb echter ook al vele varianten geprobeerd en al evenzovele foutmeldingen gehad. Het vermoeden is dat er iets fout gaat met het 2e argument van de VLookup functie.

Wie kan mij op weg helpen.

Groet,
Jan

 

[Warme bakkertje]

Ofwel gebruik je

Sheets("Plaatsingen").Range("A2:Q131")

ofwel

[Plaatsingen!A2:Q131]

 

[Jand66]

Oplossing werkt niet

Beste Warme Bakkertje,

Het wijzigen van mijn code met de door jouw geboden oplossing hoe moet ik dat exact doen. Ik heb de volgende oplossingen ondertussen geprobeerd;

ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs, Range(Sheets("Plaatsingen").Range("A2:Q131")), 1, False)

Deze geeft Error 1004 met tekst "Methode Range van object_Global is mislukt"

ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs, Sheets("Plaatsingen").Range("A2:Q131"), 1, False)

Deze geeft Error 1004 met tekst bij de initiële melding

ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs, Range[Plaatsingen!A2:Q131), 1, False)

Deze geeft Error 1004 met tekst bij de initiële melding

Heb ik de oplossingen van jouw op een verkeerde wijze ingevoegd??

Gr, Jan

 

[HSV]

Als ik het even zo snel heb bekeken, is je zoekwaarde dus ClientDs in het bereik 'PlaatsingenA2:Q131', en is de kolomindex_getal 1, wat dus inhoud dat je dezelfde waarde retour wil als ClientDS.
Dan lijkt mij:

ZoekResultaat = ClientDs

hetzelfde.

 

[Warme bakkertje]

ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs, Sheets("Plaatsingen").Range("A2:Q131"), 1, False)

ZoekResultaat = Application.WorksheetFunction.VLookup(ClientDs,[Plaatsingen!A2:Q131], 1, False)

Deze schrijfwijzen zijn beide juist.
Geeft je variabele ClientDs wel een zoekwaarde weer ? Test onderstaande eens uit want je gebruikt CurRow, maar ik zie daar nergens een definitie van in je code.

Sub tst()
Dim ClientDs As Range
Set ClientDs = Range(Cells(CurRow, 1), Cells(CurRow, 1))
Msgbox ClientDs
End Sub

 

[Jand66]

Beste HSV,

Nee, ik wil niet ClientDs terug als resultaat. Het ClientDs in de sheet "Gestarte_Trajecten" mag niet voorkomen in sheet "Plaatsingen". Ik heb in eerste instantie dan ook geprobeerd om de Vlookup als argument van CVerr te gebruiken en te bepalen of het resultaat daarvan gelijk was aan CVerr(xlErrNa), maar dat werkte niet vanwege de fout in de VLookup. Vandaar dat ik nu probeer om een waarde te vinden, maar dat lukt dus ook niet.

Beste Warme Bakkertje,

CurRow is gedefinieerd als een integer. Op het moment dat ik de regel code uitvoer is de waarde van de ClientDs "008481", zie plaatje.

Daar ligt het probleem dus niet.

Overigens wil ik jullie al wel heel hartelijk bedanken voor het meedenken over dit probleem.

Gr, Jan

 

[Jand66]

Toevoeging probleem

LS.,

In ben er net achter gekomen dat de fout zich NIET voordoet bij het verwerken van het eerste record, maar zich voordoet tijdens het verwerken van het tweede record.

Mijn excuses dat ik dit niet eerder doorhad.

Hierdoor krijgt het probleem een heel ander karakter. De syntax van de regel is dus wel goed, maar bij het voor de 2e keer aanroepen van het VLookup object kan VBA de eigenschap van WorkSheetFunction niet ophalen.

In welke richting moet ik hier de oplossing van zoeken.

Gr, Jan

 

[Warme bakkertje]

Als het je toch enkel te doen is om een controle uit te voeren of een waarde al dan niet voorkomt, waarom dan onnodig een werkbladfunctie gebruiken in VBA. Met onderstaande wordt gezochtop ClientsDs in kolom A van sheets Plaatsingen

If not Sheets("Plaatsingen").Columns(1).Find(ClientsDs,,xlvalues,xlWhole) Is Nothing Then

Vrij vertaald betekent dit: Als de waarde ClientDs gevonden wordt in kolom A dan ...

 

[Jand66]

Rudi,

Dat zou op zich mogelijk zijn voor dit deel van de functie. Er moeten echter nog een aantal controles worden uitgevoerd op de sheet "Plaatsingen" indien het nummer wel bestaat. Er moeten dan verschillende kolommen op hun waarde worden gecontroleerd, en daar heb ik dan wel Vlookup voor nodig en blijf ik tegen dit probleem aanlopen.

Als VLookup het wel doet bij het eerste record maar niet bij het tweede record dan kan dat niet aan de Vlookup functie liggen maar heeft dit volgens mij te maken met WorkSheetFunction.

Ik ben op dit moment druk aan het zoeken maar heb de oplossing nog niet gevonden.

Groet,
Jan

 

[Warme bakkertje]

Deze uitleg houd geen steek, want mijn code is het ideale uitgangspunt om de waarde op andere kolommen te controleren door gebruik te maken van Offset indien de waaarde gevonden wordt en met een Else-statement indien de waarde niet gevonden wordt.

 

[Jand66]

Rudi,

Aan de FIND functie zitten bepaald eisen t.a.v. de data welke wordt doorzocht, zie hieronder:

- - - -
Belangrijk De waarden in matrix moeten een oplopende volgorde hebben (...;-2; -1; 0; 1; 2; ...; A-Z; ONWAAR; WAAR). Als dit niet het geval is, kan ZOEKEN een onjuist resultaat geven. Bij tekstwaarden wordt geen onderscheid gemaakt tussen hoofdletters en kleine letters.
- - - -

Aangezien Plaatsingen en Gestarte Trajecten rapporten zijn uit een standaardapplicatie en niet op volgorde van dit nummer worden aangemaakt, biedt FIND onvoldoende zekerheid.

Ondertussen is de fout opgelost. Ik was vergeten de regel

On Error Resume Next"

op te nemen in de code.

Excuses voor mijn slordigheid en onoplettenheid.

Het enige positieve dat ik hier dan weer uit haal is dat wanneer iemand in de toekomst deze fout ook een keer krijgt hij op dit forum kan lezen wat een mogelijke oorzaak van zijn probleem is.

Wel heel erg bedankt voor je meedenken, is erg gewaardeerd.

Groet,
Jan

 

[Warme bakkertje]

De VBA-functie Find heeft geen uitstaans met de werkbladfunctie Zoeken :shocked:

 

[Jand66]

Rudi,

Je hebt gelijk. Ik nam aan dat FIND de equivalent was van de worksheet functie ZOEKEN, maar dat is niet juist. ZOEKEN is in VBA LOOKUP. Ook hier geldt weer een oud adagium, namelijk "Een aanname is de moeder van alle problemen".

Ik zal de functie FIND binnenkort eens nader bekijken. Dank voor de moeite.

Groet,
Jan