Formule geeft problemen met bepaalde waarde

[dompie16099]

Hoi,

Zit ik hier met een raar probleem en ik vind niet direct een oplossing, hopelijk kan iemand hier helpen.

Door een Macro wordt een formule ingevoegd in een cel die gaat controleren of de tekst in een bepaalde cel (initialen medewerkers) ook een waarde bevat in een andere cel op die rij.
Wanneer dit het geval is wordt een andere cel gekopieerd.
Ik kan dit niet met verticaal zoeken doen aangezien geen enkele lijst gesorteerd is/kan/mag worden.

Dit gaat voor alle initialen goed behalve voor de initialen ADP, hier geeft de formule #N/B voor.

Heeft iemand een idee hoe ik dit kan oplossen?

Bijgevoegd de code van de macro, het bestand zelf mag ik echter niet posten.

Sub projectnummer_overhalen()
'
' projectnummer_overhalen Macro
'
' deze macro haalt het projectnummer en de projectnaam over vanuit offertes-filter adhv de projectingenieur.
'
Dim rng As Range
Dim lMaxRows As Long

  
 
With ActiveWorkbook.Sheets(3)
    
[INDENT][INDENT]    Range("A7").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('offertes-filter'!R[6]C4=0,0,IF(INDEX(Medewerkers!R7C3:R100C5,MATCH('offertes-filter'!R[6]C4,Medewerkers!R7C5:R100C5),1)=""BB"",'offertes-filter'!R[6]C,))"
    
    Range("A7").Select
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row

    Selection.AutoFill Destination:=Range("A7:A10" & lMaxRows), Type:=xlFillDefault
    
    Range("B7").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('offertes-filter'!R[6]C4=0,0,IF(INDEX(Medewerkers!R7C3:R100C5,MATCH('offertes-filter'!R[6]C4,Medewerkers!R7C5:R100C5),1)=""BB"",'offertes-filter'!R[6]C,))"
    
    Range("B7").Select
    
    lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
    
    Selection.AutoFill Destination:=Range("B7:B10" & lMaxRows), Type:=xlFillDefault
  
     .Rows(1).Insert Shift:=xlDown
 
 [/INDENT]   .Range("A7:A10" & lMaxRows).AutoFilter Field:=1, Criteria1:="0"
 '
    With .AutoFilter.Range
 
  [INDENT]      On Error Resume Next
 
        Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
 
        On Error GoTo 0
 
 [/INDENT]       If Not rng Is Nothing Then rng.EntireRow.Delete
 
    End With
 
    .AutoFilterMode = False
 
    .Rows(1).EntireRow.Delete
 [/INDENT]
End With

     
End Sub

Alvast bedankt voor de hulp

 

[MJONK]

zonder voorbeeldbestandje wordt dit lastig, kun je hem niet ontleden?

Verder, heb je het geprobeerd met vertikaal zoeken? sortering is niet altijd nodig.

 

[dompie16099]

Ik weet dat het zonder bestandje moeilijk gaat worden en zal morgen proberen mijn bestand onherkenbaar te maken.

voor verticaal zoeken zoekt excel altijd in de meest linkse kolom van een bereik, ik moet echter in de meest rechtse kolom zoeken en de meest linkse al uitkomst kunnen geven

 

[MJONK]

In dat geval kun met de formules INDEX en VERGELIJKEN een hoop bereiken! (als je niks aan het bestand mag veranderen....)

Andere mogelijkheid is nog om tijdelijk naar de meest linkse kolom te verwijzen in de kolom rechts van de meest rechtse ,

Hoop dat je me nog snapt!

 

[dompie16099]

Met die index en vergelijk kom ik er niet echt uit (zal wel aan mezelf liggen hoor )

Wat die 2de tip betreft ga ik eens verder mee spelen en wat verborgen cellen voorzien

 

[Cobbe]

Ik heb je een voorbeeld gemaakt met toepassing van de Index-Vergelijken formule.

 

[dompie16099]

Hoi Cobbe,

Alvast bedankt, ik ga hier deze week verder mee aan de slag en hou jullie op de hoogte

 

[dompie16099]

Hoi,

Met een combinatie van de 2 oplossingen (de indexformules in een verborgen rij en die door een macro laten kopieren) werkt het nu bijna perfect. :thumb:
Het enige probleempje dat ik heb is om het einde van de range te bepalen tot waar hij de formules moet kopieren.
Er moet gestopt worden met kopieren op het moment dat de laatste rij op het eerste werkblad van de file bereikt is.

Dit is de code die ik nu gebruik (al een hele verbetering tegenover het origineel):

Sub projectnummer_overhalen()
'
' projectnummer_overhalen Macro
'
' deze macro haalt het projectnummer en de projectnaam over vanuit offertes-filter adhv de projectingenieur.
'
Dim rng As Range
Dim lMaxRows As Long

  
 
With ActiveWorkbook.Sheets(3)
   
    Range("A8:I65536").ClearContents
       
    Range("A7").Select
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("A7:I7").Select
    Selection.AutoFill Destination:=Range("A7:I13" & lMaxRows), Type:=xlFillDefault
 '   Range("A7:I13").Select
    Range("A8").Select
   
    .Rows(1).Insert Shift:=xlDown
 
    .Range("A8:A10" & lMaxRows).AutoFilter Field:=1, Criteria1:="0"
 '
    With .AutoFilter.Range
 
        On Error Resume Next
 
        Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
 
        On Error GoTo 0
 
        If Not rng Is Nothing Then rng.EntireRow.Delete
 
    End With
 
    .AutoFilterMode = False
 
    .Rows(1).EntireRow.Delete
 
End With

     
End Sub

 

[dompie16099]

Het meeste werkt dus naar behoren (op het probleem met de laatste rij ben ik nog aan het kauwen) maar toch blijken de initialen ADP soms nog problemen te geven (niet constant).
Zou het kunnen dat dit een verborgen functie is in excel??

 

[WHER]

op het probleem met de laatste rij ben ik nog aan het kauwen

Ik schat dat je laatste-rij-probleem voortkomt uit de combinatie van deze twee regels:

 lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row

en

Range("A8:A10" & lMaxRows).

test in je Immediate Window eens wat voor resultaat je krijgt met

?Range("A8:A10" & lMaxRows).Address

 

[dompie16099]

Hoi Wher,

dit is het resultaat dat ik dan krijg:

?Range("A8:A10" & lMaxRows).Address
$A$8:$A$10

 

[WHER]

En je deed deze test terwijl je met F8 de code doorloopt en op volgende regel bent aanbeland:

.Range("A8:A10" & lMaxRows).AutoFilter Field:=1, Criteria1:="0"

 

[dompie16099]

Dan krijg ik idd een ander resultaat ( ben nog maar net met macro's begonnen )

?Range("A8:A10" & lMaxRows).Address
$A$8:$A$106

Als ik op dat moment in de formule van de laatst ingevulde rij ga kijken verwijst die naar A141

 

[dompie16099]

Toch nog even een update posten:

Ondertussen heeft een andere collega (die tenminste iets kent van VBA) de boel herschreven en nu werkt alles
Toch bedankt voor de hulp iedereen!