#NV bei VLookup abfangen

[Heinz Ulm]

Hallo Forumsfreunde,

ich stehe gerade mal wieder auf dem Schlauch.

In einer VBA-Schleife lasse ich mir von einer Mappe in eine andere Mappe Werte eintragen.

Mit

SW = WorksheetFunction.VLookup(wert, Range("A:Z"), 9, 0)

klappt das auch einwandfrei.

nun bin ich auf dem Weg der Verbesserung des Codes und habe einen "Wert" geändert, sodass er nicht mehr mit dem Wert der anderen Mappe übereinstimmt. 

Folge: Der Code blockiert bei SW (SW wird als leer im Code angezeigt) müsste aber #NV sein.

In diesem Falle sollte nur die Zelle rot werden.
Wie fange ich nun am besten dieses blöde #NV ab.

Vielen Dank für eure Hilfe

[Egon12]

sorry falsch gedacht.

[Klaus-Dieter]

Hallo Heinz,

magst du mal deinen aktuellen Stand hochladen? Dann braucht man das nicht nachbauen, in der Zeit kann man schon einen Lösungsvorschlag entwickeln.

[Heinz Ulm]

Hallo Klaus-Dieter.

kann ich machen: Hier der Code bisher:

Sub werteHolen()

Dim Wb As Workbook, i As Integer
Dim vntAndereMappennamen() As Variant
Dim Alt As String
Dim Neu As String


'Festlegen des Namens des ST smartsheets
    Neu = ThisWorkbook.Name

' Überprüfen der Anzahl der offenen Arbeitsmappen
    Anzahl = Workbooks.Count
   
'Überprüfen ob nur 1 oder mehr als 2 Smartsheets offen sind
    If Workbooks.Count = 1 Then MsgBox "Keine andere Mappe offen!": Exit Sub

    If Workbooks.Count > 2 Then
        MsgBox "Bitte alle Mappen bis auf die 2 Smartsheets schließen"
        Exit Sub
    End If

ReDim vntAndereMappennamen(1 To Workbooks.Count - 1)

'feststellen des anderen Smartscheetsnamen und in die K10 des Pricescheets schreiben
    Range("K10").Value = ""
   
    For Each Wb In Workbooks
        Alt = Wb.Name
        If Wb.Name <> Neu Then
            Range("K10").Value = Alt
        End If
    Next Wb
   
    Alt = Range("K10").Value
 
' Prüfen der Version
    Windows(Alt).Activate
    VersionAlt = Worksheets("coversheet").Range("H23").Value
        DatumAlt = DateValue(Right(VersionAlt, 10))
    Windows(Neu).Activate
    Versionneu = Worksheets("coversheet").Range("H23").Value
        DatumNeu = DateValue(Right(Versionneu, 10))
    If DatumNeu < DatumAlt Then
    MsgBox ("Falsche Mappe soll bearbeitet werden")
    Exit Sub
    End If
   
    Windows(Alt).Activate
   
'Bestimmen der Menge der Einträge auf dem Pricesheet
'in Spalte K für die Schleife des Sverweises.

    Sheets("pricesheet").Select
    k = Cells(Rows.Count, 1).End(xlUp).Row

'Schleife zum Holen der neuen Preise
    For i = 15 To 21 'k
    wert = Range("A" & i + 1).Value 
    Windows(Neu).Activate,
    WertNeu = Range("A" & i + 1).Value
    Worksheets("Pricesheet").Select

'Abfangen eines nicht vorhandenen Entrags
    If wert = WertNeu Then 'Hier habe ich mir mal den alten Wert und den Vergleichswert geholt, wenn die gleich sind Sverweis, sonst Färben, das Klappt so
'SVERWEIS für den neuen Preis
        SW = WorksheetFunction.VLookup(wert, Range("A:Z"), 9, 0)
        Windows(Alt).Activate
        Sheets("pricesheet").Select
        Range("I" & i + 1).Value = SW
    Else
        Windows(Alt).Activate
        Sheets("pricesheet").Select
        Range("I" & i + 1).Select
        Selection.Interior.Color = 255
    End If
    Next i

'Anzahl der Lots
    Windows(Alt).Activate
    Sheets("Coversheet").Select
    Lots = WorksheetFunction.CountA(Range("E6:E56")) + 5
   
    For L = 6 To Lots
'        Lot = Sheets("Coversheet").Range("E" & 6).Value
        Sheets("pricesheet").Range("I3").Value = L - 5
       
'        Korrektur
    Next L

End Sub



Danke für dein Interesse an meinem Problem

[Klaus-Dieter]

Hallo Heinz,

vielen Dank, ich hatte mehr an die Datei gedacht.

[{Boris}]

Hi Heinz,

Folge: Der Code blockiert bei SW (SW wird als leer im Code angezeigt) müsste aber #NV sein.
SW = WorksheetFunction.VLookup(wert, Range("A:Z"), 9, 0)

Das glaube ich nicht, denn der Code müsste abfliegen und sich der Debugger zu Wort melden, wenn der Suchbegriff denn tatsächlich nicht vorhanden ist, da Du keine Fehlerbehandlung drin hast.

Fazit: Zeig doch mal bitte ein konkretes Beispiel (in einer kleinen Beispieldatei), so wie es Klaus-Dieter bereits angeregt hat.

[Heinz Ulm]

Hallo Boris,

du hast recht, 
es kommt auch der Debugger und meldet mir die Zeile SW = gelb.

Eben diese Fehlerroutine suche ich.

Meine Krücke mit dem Vergleich der Werte Zeilenweise funktioniert auch

[{Boris}]

Hi Heinz,

quick & dirty z.B.:

On Error Resume Next
SW = WorksheetFunction.VLookup(wert, Range("A:Z"), 9, 0)
On Error GoTo 0


Andernfalls z.B. zunächst den Suchbegriff wert mit der Find-Methode (oder auch WorksheetFunction.CountIf) suchen und den SVERWEIS nur bei einem Treffer ausführen.

[Klaus-Dieter]

Hallo Heinz,

magst du das mal testen?:

Code:

'Bestimmen der Menge der Einträge auf dem Pricesheet
'in Spalte K für die Schleife des Sverweises.

   
    k = Sheets("pricesheet").Cells(Rows.Count, 1).End(xlUp).Row

'Schleife zum Holen der neuen Preise
    For i = 15 To 21 'k
        wert = Range("A" & i + 1).Value
        WertNeu = Windows(Neu).Worksheets("Pricesheet").Range("A" & i + 1).Value


'Abfangen eines nicht vorhandenen Entrags
        If wert = WertNeu Then
'SVERWEIS für den neuen Preis
            SW = WorksheetFunction.VLookup(wert, Range("A:Z"), 9, 0)
            If Len(SW) > 0 Then
            Windows(Alt).Sheets("pricesheet").Range("I" & i + 1).Value = SW
        Else
            Windows(Alt).Sheets("pricesheet").Range("I" & i + 1).Interior.Color = 255
            Z = Z + 1
        End If
    Next i

Habe gleich mal versucht, den Quelltext zu optimieren. (Entfall von Activate und Select)

[Heinz Ulm]

Hallo Klaus-Dieter,

ich habe es jetzt auf diese Weise gelöst:

'Abfangen des #NV-Ergebnisses und SVERWEIS für den neuen Preis

    Ergebnis = IsError(Application.VLookup(wert, Range("A:Z"), 9, 0))
'Wenn es kein Fehler ist:
        If Ergebnis = "Falsch" Then
            SW = Application.VLookup(wert, Range("A:Z"), 9, 0)
'Eintragen des neuen Preises
            Windows(Alt).Activate
            Sheets("pricesheet").Select
            Range("I" & i + 1).Value = SW
'Bei Fehler Zelle Rot färben
        Else
            Windows(Alt).Activate
            Sheets("pricesheet").Range("I" & i + 1).Interior.Color = 255
            Z = Z + 1
        End If
'Weiter in der Schleife
    Next i


 Mit deiner Zusammenfassung
 Windows(Alt).Sheets("pricesheet").Range("I" & i + 1).Value = SW
mault bei mir VBA immer.
Dann lass ich es halt auf mehreren Zeilen.

Danke