Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Datenzeile überschreiben, wenn Prüfwert vorhanden


Betrifft: Datenzeile überschreiben, wenn Prüfwert vorhanden von: Dietmar
Geschrieben am: 13.04.2018 15:04:48

Hallo in die Runde,

in meiner kürzlichen Frage vom 17.3.2018 habe ich u.a. folgenden Code erhalten, um Daten in eine geschlossene Datei zu übertragen.
Funktioniert bestens. Dafür nochmal ganz herzlichen Dank an Hajo und Thorsten alias Nepumuk!

Ich möchte nun noch eine Prüfung vorschalten, ob der Grunddatensatz bereits vorhanden ist; denn dann soll eine ÜBERSCHREIBUNG stattfinden.

Zur Prozedur:

1)
VOR dem nachfolgende aufgeführten Code-Schnipsel wird aus dem ExcelSheet ein PDF erzeugt.
Der Speicherort ist als 'strTMP' deklariert.

2)
Danach erfolgt der Übertrag in das geschlossene Workbook und aus strTMP wird der Hyperlink wie folgt erzeugt:

Set WkSh_Q = ThisWorkbook.Worksheets("Mangel-Erfassung")
Set WkSh_Z = Workbooks(sDatei).Worksheets("Datenpool")

'Übertrag des Copy-Bereiches in nächste leere Zeile (In Spalte A wird zunächst nur ein  _
Platzhalter eingetragen; dort wird gleich der Hyperlink eingetragen)    
    WkSh_Q.Cells.Range("M1:AE1").Copy
        With WkSh_Z
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
 
'Jetzt Eintrag des Hyperlinks, der sich aus dem Speicherort des zuvor (hier nicht angezeigten)  _
erzeugten PDFs ergibt (strTMP):
    With WkSh_Z
            lngLastRow = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp). _
Row, .Rows.Count) + 0
        .Cells(lngLastRow, 1).Hyperlinks.Add Anchor:=.Cells(lngLastRow, 1), Address:=strTMP & ". _
pdf", TextToDisplay:=strTMP
    End With

Es läuft also in 2 Schritten ab: Zuerst werden die Daten ab Spalte B in die nächste leere Zeile der Zieldatei (Datenpool) übertragen und danach wird in Spalte A der GLEICHEN Zeile (da ist zunächst ein Platzhalter eingetragen) der Hyperlink eingetragen, der sich aus dem Speicherort des PDFs zusammensetzt (hier: strTMP).

3)
Soweit alles bestens. Jetzt mein Anliegen:

Wenn der Hyperlink (Spalte A) bereits vorhanden ist, dann soll die Zeile in der Zieldatei komplett ÜBERSCHRIEBEN werden. Es braucht also nichts ergänzt werden, sondern es kann überschrieben werden, weil aus der Quelldatei immer alle Daten dabeisein werden.
Ich möchte den Hyperlink als Prüfwert nehmen, weil er eine eindeutige Prüfung ermöglicht.

Wie müsste der Code dann lauten?

Wie immer bin ich sehr gespannt und freue mich auf einen Lösungsvorschlag.

VG
Dietmar

  

Betrifft: Mein Versuch ... von: Dietmar
Geschrieben am: 13.04.2018 22:04:11

Hallo zusammen,

ich habe noch etwas getüftetl und folgenden Ansatz gefunden. Komme aber nicht weiter.

Und zwar prüfe ich mit folgendem Code, in welcher Zeile in Spalte A der Hyperlink (strTMP) bereits vorkommt.

    Dim c As Range
    Set c = WkSh_Z.Range("A:A").Find(strTMP, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        MsgBox "Wert ist vorhanden in der Zeile " & c.Row
Das funktioniert auch und wird mir in der MsgBox ausgeworfen.

Wie muss ich den zweiten Teil des folgenden Code anpassen, damit die kopierten Werte genau in die Zeile eingetragen werden (das passiert ja ab Spalte B), in der in Spalte A der Hyperlink mit obiger Prüfung gefunden wurde?
    WkSh_Q.Cells.Range("M1:AE1").Copy

        With WkSh_Z
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
Bekomme es leider nicht hin.
Oder bin ich mit meinem Lösungsansatz völlig auf dem Holzweg?

Vielen Dank vorab!

VG
Dietmar


  

Betrifft: AW: Mein Versuch ... von: Werner
Geschrieben am: 13.04.2018 22:44:13

Hallo Dietmar,

so:

.Cells(c.Row, 2).PasteSpecial Paste:=xlPasteValues,  _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Gruß Werner


  

Betrifft: Klappt super! von: Dietmar
Geschrieben am: 13.04.2018 23:37:17

Hallo Werner,

Du hast mir meinen Abend versüßt :-)

Das passt!
Damit ist meine Anfrage erledigt. Auf diesem Wege komme ich klar.

Herzlichen Dank!

VG
Dietmar


  

Betrifft: Gerne u. Danke für die Rückmeldung. o.w.T. von: Werner
Geschrieben am: 14.04.2018 09:11:25




Beiträge aus dem Excel-Forum zum Thema "Datenzeile überschreiben, wenn Prüfwert vorhanden"