Office: VBA Sverweis Phänomen

Hallo!

Ich habe ein Phänomen, welches mir gar nicht logisch in den Kopf will...
via folgendem VBA Code setze ich einen SVERWEIS in ein sich ständig ans Datum angepassende andere excel Sheet. Das mir unverständliche ist der Umstand, das wenn die Formel in die Zelle einegsetzt wird "#NAME?" erscheint. Wenn ich aber die syntaktische eigentlich korrekte Formel in der Zelle mit "F2" editiere und gleich wieder speichere ohne etwas zu verändern dann funktioniert der Sverweis. Wesentlich an der Sache ist, das in der Zelle am ende auch eine Formel stehen, also nicht nur der Sverweis einmalig eingetragen werden soll.

Code:

Option Explicit
Sub Zelle_auslesen()
   Dim LUVsheetName As String, Wert1 As Date, Counter As Integer, Path1 As String, Datei1 As String, strvar(10) As Long, dashier As Workbook, LUV As Workbook
   Dim letzteZeile As Long, Zeile As Long, test, Zellbezug As String
   Set dashier = ThisWorkbook
   
   Wert1 = Date: Counter = 0: Path1 = "i:\Patenlisten_2do\H141\LUV\": Datei1 = "Paten_LUV_mit_Kommentar_original Arbeitsdatei für  Patengespräch.xls"
    LUVsheetName = "Lfde LUV-Aufträge_" & Format(Wert1, "dd.mm.yyyy")
   While GetDataClosedWB(Path1, Datei1, LUVsheetName) = False
   Counter = Counter - 1: Wert1 = Date + Counter
   LUVsheetName = "Lfde LUV-Aufträge_" & Format(Wert1, "dd.mm.yyyy")
   Wend
   With dashier.Sheets(1)
   If .Range("A1") <> LUVsheetName Then
   .Range("A1") = LUVsheetName
   End If
   End With
strvar(3) = Application.Match("P-Geh", Rows(4), 0): strvar(2) = Application.Match("Pos", Rows(4), 0): strvar(1) = Application.Match("Belnr", Rows(4), 0)
   strvar(4) = Application.Match("Einteilungsdatum", Rows(4), 0): strvar(5) = Application.Match("Mo1", Rows(4), 0)
   letzteZeile = dashier.Sheets(1).Range("A65336:F65336").End(xlUp).Row
   For Zeile = 5 To letzteZeile
   If dashier.Sheets(1).Cells(Zeile, strvar(1)) <> "" And dashier.Sheets(1).Cells(Zeile, strvar(2)) <> "" And dashier.Sheets(1).Cells(Zeile, strvar(3)) <> "" Then
   
'sverweis Einteilungsdatum,Mo1
   dashier.Sheets(1).Cells(Zeile, strvar(4)).Select
   ActiveCell.FormulaR1C1 = _
   "=IF(ISNA(VLOOKUP(RC1,'" & Path1 & "[" & Datei1 & "]" & LUVsheetName & "'!A:AE,25,0)),"""",VLOOKUP(RC1,'" & Path1 & "[" & Datei1 & "]" & LUVsheetName & "'!A:AE,25,0))"
   
   'dashier.Sheets(1).Cells(Zeile, strvar(4)).Formula = "=IF(ISNA(VLOOKUP(" & Range("A" & strvar(4)).Address & ",'" & Path1 & "[" & Datei1 & "]" & LUVsheetName & "'!$A:$AE,25,0)),"""",VLOOKUP(" & Range("A" & strvar(4)).Address & ",'" & Path1 & "[" & Datei1 & "]" & LUVsheetName & "'!$A:$AE,25,0)"
   Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Application.ScreenUpdating = True
   End If
   If dashier.Sheets(1).Cells(Zeile, 1) = "" Then
   dashier.Sheets(1).Cells(Zeile, 1).Select
   ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
   
   End If
   Next Zeile
      
End Sub
Private Function GetDataClosedWB(SourcePath As String, SourceFile As String, sourceSheet As String) As Boolean
Dim Zellbezug As String, Ergebnis As Variant, Adresse As String
   Zellbezug = Cells(1, 2).Address(ReferenceStyle:=xlR1C1)
   Adresse = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Zellbezug
   On Error GoTo ERROR_HANDLING
   Ergebnis = ExecuteExcel4Macro(Adresse)
If Ergebnis = "Fehler 2023" Then
GetDataClosedWB = False
Else
GetDataClosedWB = True
End If
GoTo ende
ERROR_HANDLING:
If Err.Number = 13 Then 'das war die originale Fehlernummer
Resume Next
End If
MsgBox Err.Description + vbNewLine + vbNewLine + _
Str$(Err.Number)
Resume Next
ende:
End Function
Private Function KW(d As Variant) As String
    Dim t As Date
Application.Volatile
If IsDate(d) Then
    t = DateSerial(Year(d - Weekday(d, 2) + 4), 1, 4)
    KW = "KW" & Format((d - t + Weekday(t, 2) - 1) \ 7 + 1, "00") & "/" & Year(d)
    End If
End Function

Die KW Funktion wird aus der jeweiligen Zelle aufgerufen hat also nur indirekt mit dem Makro etwas zu tun (ausser dem Bestandteil Volatile)

Vielen Dank für die Hilfe

 

Hallo!

Mag das vielleicht daran liegen, dass du in deiner Formel sowohl mit A1-Bezügen wie auch mit R1C1-Bezügen arbeitest?

 

Hi Andy,

vielleicht hilft, wenn du ans Ende deines Codes die Zeile

Code:

Application.Calculate 

schreibst.

Bis später,
Karin

 

Hallo,

Die Fehlermeldung #NAME? in einer Arbeitsblattzelle hat in der Regel damit zu tun, dass ein in der Zelle enthaltener Funktionsname nicht gefunden wird. Und das hängt wieder damit zusammen, dass Du die KW-Funktion Private deklariert hast: damit ist aber dieser Name außerhalb des deklarierenden Moduls unbekannt (er kann nur innerhalb des Moduls verwendet werden). Bei Verwendung der Funktion im Arbeitsblatt muss sie stets Public deklariert sein.
(Außerdem - nur der Vollständigkeit halber - muss eine solche Arbeitsblatt-Funktion in einem allgem. Codemodul deklariert werden).

 

@schatzi: werde ich noch testen ich wäre aber verwirrt wenn das so wäre

@Beverly: hat nichts gebracht

@Exl121150: hat nichts gebracht, ich war bisher immer der Annahme, das sich PRIVATE auf dieses Workbook bezieht und Public übergreifend auf alle geöffneten.

Trotzdem Danke!

 

Hi Andy,

an welche Stelle genau hast du Application.Calculate geschrieben? Es sollte in die Prozedur Sub Zelle_auslesen vor die Zeile End Sub.

Bis später,
Karin

 

ja

Code:

 ...

  End If
   Next Zeile

Application.Calculate
End Sub