[Excel 2010][VBA] variable lookup (fout 1004)

[Mastha-Hacker]

Hi,

Ik moet voor mijn stage nog 1 excel-macro maken. Deze lukt al een stuk beter dan de vorige. Ik loop nu alleen op het volgende probleem vast:
Ik heb een werkblad (Imported) met verschillende gegevens. Het aantal regels verschilt vaak sterk. Kolom aantal is gelijk. Kolom A bevat de projectnummers.
Ik heb een werkblad (ImportedExt) welke 3 kolommen bevat. De 1e kolom bevat een projectnummer.
De gegevens van ImportedExt uit kolom 2 en 3 moeten in het blad Imported op de juiste plaats terecht komen. (Bij de overeenkomende projectnummers) Het kan alleen zijn dat een projectnummer niet in ImportedExt voorkomt. Ik heb tot nu toe een aantal dingen geprobeerd, maar krijg telkens foutcode 1004. Dit is mijn huidige code:

Visual Basic .NET:

Sub ChainData()

    Sheets("Imported").Select
    Range("J2").Value = Application.WorksheetFunction.VLookup(1, Worksheets("ImportedExt").Range("A1:D99999"), 1, False)
   ' InstallDate = Application.WorksheetFunction.VLookup(Val(Project), Worksheets("ImportedExt").Range("A1:D99999"), 1, False)

End Sub

Per rij moet eigenlijk deze excel functie worden uitgevoerd:

code:

=VERT.ZOEKEN($A2;ImportedExt!$A$1:$C$9999;2;ONWAAR)

[MrAngry]

Ik weet niet waarom je die fout krijgt, maar probeer die range eens wat kleiner te zetten. Wellicht dat het problemen geeft omdat ie groter is dan 65536, maar dat hangt wellicht ook een beetje van je versie af.

[Mastha-Hacker]

MrAngry schreef op maandag 18 juli 2011 @ 15:05:
Ik weet niet waarom je die fout krijgt, maar probeer die range eens wat kleiner te zetten. Wellicht dat het problemen geeft omdat ie groter is dan 65536, maar dat hangt wellicht ook een beetje van je versie af.

Getest, maar blijft dezelfde fout geven. Ook al doe ik het met een range tot 600 rijen.

[Yohsoog]

Misschien dat je ook even de foutomschrijving er bij kan geven ipv enkel het nummer zodat niet iedereen deze moet opzoeken.

Maar mijn eerste hit via google was dus: Copying worksheet programmatically causes run-time error 1004 in Excel

Heb je dat geprobeerd (ook al is het voroal 2007 en 2003) ?

[Mastha-Hacker]

Yohsoog schreef op maandag 18 juli 2011 @ 15:32:
Misschien dat je ook even de foutomschrijving er bij kan geven ipv enkel het nummer zodat niet iedereen deze moet opzoeken.

Maar mijn eerste hit via google was dus: Copying worksheet programmatically causes run-time error 1004 in Excel

Heb je dat geprobeerd (ook al is het voroal 2007 en 2003) ?

Dat is inderdaad wel handiger.. De foutmelding is:

code:

Fout 1004 tijdens uitvoering:

Eigenschap VLookup van klasse WorksheetFunction kan niet worden opgehaald.

[MrAngry]

Als je googled op de Engelse versie van je foutmelding (Unable to get the vlookup property of the Worksheet function class) dan zie je dat je die ook krijgt in het geval van geen match in je vlookup. Je moet dus even een stukje macro schrijven dat die fout afvangt.

[Mastha-Hacker]

Die formule klopt naar mijn gevoel niet. De waarde en zoek-waarde voor J2 bestaat namelijk gewoon. (Deze werkt wel met de vert.zoeken excel functie.

[MrAngry]

Dan die eerste 1, vervangen door Range("cellwaardie1naarverwijst")

[Verwijderd]

Waarom niet gewoon de formule zelf in de cel plaatsen, ipv het resultaat van de formule vanuit Application.WorksheetFunction?

[Mastha-Hacker]

Verwijderd schreef op maandag 18 juli 2011 @ 18:01:
Waarom niet gewoon de formule zelf in de cel plaatsen, ipv het resultaat van de formule vanuit Application.WorksheetFunction?

Hoe bedoel je? De formule vert.zoeken in het sheet invullen? Dan is de sheet niet dynamisch genoeg. Hij moet achter elke gevulde regel de juiste waarden neerzetten..

MrAngry schreef op maandag 18 juli 2011 @ 16:44:
Dan die eerste 1, vervangen door Range("cellwaardie1naarverwijst")

Blijft dezelfde fout geven.

[Verwijderd]

"Niet dynamisch genoeg"? Dat snap ik niet. Leg eens uit waarom een formule voor jouw probleemstelling niet dynamisch (genoeg) is, maar een vaste waarde die je vanuit een VBA-script in een cel zet wel?

[ Voor 7% gewijzigd door Verwijderd op 19-07-2011 11:09 ]

[Mastha-Hacker]

Het aantal rijen is variabel. Dus ik kan niet in elke cel een formule zetten (handmatig)

Ik heb 2 sheets met data. De data uit sheet 2 moet achter het juiste projectnummer in sheet 1.

Dat ik een vaste waarde in het vba-script had gezet was alleen voor testen, en omdat ik geen flauw idee heb hoe ik er voor kan zorgen dat hij de formule voor elke rij uitvoert (tot en met de laatste rij waar data in zit)

Ik ben op internet een functie val() tegen gekomen, maar heb niet kunnen vinden waar de naam gedefinieerd wordt.

[G8rb8n]

Waarom maak je geen gebruik van Application.Match? Bijvoorbeeld:

ProjectRow=Application.Match(Sheets("Imported").Cells(2,1),Sheets("ImportedExt").Columns(1),0)

If Iserror ProjectRow Then
'wat te doen als het project niet bestaat
Else
Sheets("Imported").Cells(2,10)=Sheets("ImportedExt").Cells(ProjectRow,2)
End If

Eventueel nog een loopje toepassen om alle rijen langs te lopen

[Mastha-Hacker]

G8rb8n schreef op dinsdag 19 juli 2011 @ 11:39:
Waarom maak je geen gebruik van Application.Match? Bijvoorbeeld:

ProjectRow=Application.Match(Sheets("Imported").Cells(2,1),Sheets("ImportedExt").Columns(1),0)

If Iserror ProjectRow Then
'wat te doen als het project niet bestaat
Else
Sheets("Imported").Cells(2,10)=Sheets("ImportedExt").Cells(ProjectRow,2)
End If

Eventueel nog een loopje toepassen om alle rijen langs te lopen

Thnx! Het stukje werkt nu half.

Maar wat raar is, is dat de code het projectnummer nu alleen pakt als ik het projectnummer heb gekopieerd van sheet 1 naar sheet 2. Terwijl het projectnummer exact hetzelfde lijkt. (zelfde getal, zelfde cel-stijl)
En de code werkt ook als ik dubbel klik op een projectnummer cel in sheet 1 (zodat de cursor zichtbaar wordt), en daarna weer op [ENTER] druk.

Weet iemand wat er dan mis is met de cellen van Sheet 1? Wat voor verschil er dan tussen kan zitten? en hoe dat op te lossen is?

Dit is trouwens de code geworden:

Visual Basic .NET:

Sub ChainData()

    Sheets("Imported").Select
    
   LastRow = Range("A:A").End(xlDown).Row
   
   For RCounter = 2 To LastRow Step 1
   
    ProjectRow = Application.Match(Sheets("Imported").Cells(RCounter, 1), Sheets("ImportedExt").Columns(1), 0)
    
    If IsError(ProjectRow) Then
     Sheets("Imported").Cells(RCounter, 10).Value = "Niet Beschikbaar"
    Else
     Sheets("Imported").Cells(RCounter, 10) = Sheets("ImportedExt").Cells(ProjectRow, 2)
     Sheets("Imported").Cells(RCounter, 11) = Sheets("ImportedExt").Cells(ProjectRow, 3)
    End If
   
   Next RCounter
   

End Sub

[G8rb8n]

Projectnummer is opgeslagen als tekst? Ook al zet je de celopmaak om van algemeen naar getal, dan blijft het tekst. Op beide tabbladen de cellen omzetten naar getal via Tekst naar kolommen zou dit op moeten lossen.

[Mastha-Hacker]

G8rb8n schreef op dinsdag 19 juli 2011 @ 12:53:
Projectnummer is opgeslagen als tekst? Ook al zet je de celopmaak om van algemeen naar getal, dan blijft het tekst. Op beide tabbladen de cellen omzetten naar getal via Tekst naar kolommen zou dit op moeten lossen.

Fixed! Foutje met het importeren. Ik had het ene sheet als algemeen geimporteerd, en de ander als text..

Thnx!

Nu nog een paar sorteringen, en dan ben ik klaar met de macro.

offtopic:
En met Excel/Windows! Kan ik eindelijk volledig over op Apple

[Verwijderd]

Mastha-Hacker schreef op dinsdag 19 juli 2011 @ 11:32:
Het aantal rijen is variabel. Dus ik kan niet in elke cel een formule zetten (handmatig)

Eenvoudig op te lossen door de aangeleverde data als tabel te formatteren. Dan wordt het aantal regels waarin je een formule hebt staan ook automatisch aangepast aan het aantal regels uit je bronaanlevering.

Heb je geen VBA voor nodig

[Mastha-Hacker]

Verwijderd schreef op dinsdag 19 juli 2011 @ 13:33:
[...]


Eenvoudig op te lossen door de aangeleverde data als tabel te formatteren. Dan wordt het aantal regels waarin je een formule hebt staan ook automatisch aangepast aan het aantal regels uit je bronaanlevering.

Heb je geen VBA voor nodig

Zoals de macro nu is, is die een stuk veiliger.
Wel bedankt voor de tip! Zoals de macro nu is, kun je 4 posten hierboven lezen.

[Mastha-Hacker]

Ik heb nog 1 vraag. Het heeft ook te maken met variabelen, dus daarom houd ik het even in dit topic.

Ik moet een aantal ranges aan nummers zoeken in het sheet, en deze moeten verwijderd worden.
De ranges:
van 29920000 t/m 29920999
van 451001 t/m 452999

En er zijn nog een paar van deze ranges.

Ik had in gedachten om die ranges te verwijderen met

Visual Basic .NET:

ActiveSheet.Range(Selection, ...).AutoFilter Field:=4, Criterial:=Array(Range("29920000:29920999"),Range("451001:452999"))
Range(Selection, ....)
Selection.Delete  Shift:=xlUp

Alleen ben ik er achter gekomen dat je Range() alleen voor cellen/rijen/kolommen kunt gebruiken..

[Verwijderd]

Het Range-object is inderdaad niet wat jij dacht dat het was...

Maar volgens mij moet je in het criteria-argument van de autofilter-methode van het range-object toch ook een expressie kunnen ingeven? Je zou de documentatie er eens op na moeten slaan: gewoon met de cursor op AutoFilter gaan staan en op F1 drukken. Heeft mij al vaak genoeg geholpen

Daarnaast moet je ook eens met variabelen gaan werken waaraan je je Range-objecten toekent. Dat valt altijd te prefereren boven de Select-methode en het Selection-object die de macrorecorder erop nahoudt.

[ Voor 22% gewijzigd door Verwijderd op 21-07-2011 09:21 ]

[Mastha-Hacker]

Ik kom er niet echt uit.. (Lees: Ik kan het ng niet vinden )

[Reptile209]

Normaal (in een werkblad) zou je dan als filter-criteria gaan werken met ">=29920000" en "<=29920999". Daarmee definieer je dan je 'range' van getallen.

[Mastha-Hacker]

Reptile209 schreef op donderdag 21 juli 2011 @ 11:42:
Normaal (in een werkblad) zou je dan als filter-criteria gaan werken met ">=29920000" en "<=29920999". Daarmee definieer je dan je 'range' van getallen.

Zou je een voorbeeld kunnen geven?

[Reptile209]

[google=excel vba autofilter] > eerste link is deze.

[Mastha-Hacker]

Reptile209 schreef op donderdag 21 juli 2011 @ 12:29:
[google=excel vba autofilter] > eerste link is deze.

Thnx!