WENNFEHLER mit INDIREKT in bedingter Formatierung

[Andreas Killer]

Hallo,

ich hab da ein kurioses Verhalten bei einem Tool welches dazu dient die Unterschiede in 2 CSV-Dateien mit einer bedingten Formatierung zu visualisieren. Vereinfachtes Beispiel:

Diese beiden CSV-Dateien werden (von einem ERP) in Excel geöffnet:

  220001001.csv (Größe: 34 Bytes / Downloads: 5)

  220001002.csv (Größe: 41 Bytes / Downloads: 5)

Das Tool ist eine 3te Exceldatei mit mind. 2 Blättern. In dieser Datei läßt man nun dieses Makro laufen:

Code:

Option Explicit

Dim DataL As Range, DataR As Range

#Const Version = 1

Private Function BaseRef(ByVal R As Range) As String
  Dim Ref As String
  Ref = R.Address(0, 0, External:=True)
  BaseRef = Left$(Ref, InStrRev(Ref, "!"))
End Function

Sub Main()
  Set DataL = Workbooks("220001001.csv").Sheets(1).Range("A1").CurrentRegion
  Set DataR = Workbooks("220001002.csv").Sheets(1).Range("A1").CurrentRegion

#If Version = 1 Then
  Const DefFormula = "=INDIREKT(""[LRef]""&ADRESSE(ZEILE();SPALTE()))<>INDIREKT(""[RRef]""&ADRESSE(VERGLEICH(INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0);SPALTE()))"
#End If
#If Version = 2 Then
  Const DefFormula = "=WENNFEHLER(INDIREKT(""[LRef]""&ADRESSE(ZEILE();SPALTE()))<>INDIREKT(""[RRef]""&ADRESSE(VERGLEICH(INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0);SPALTE()));1)"
#End If
 
  Dim LRef As String, RRef As String, Formula As String
  Dim RRefB As String, LRefB As String
 
  Dim FC As FormatCondition
  Dim Data As Variant 'Range
  Dim i As Integer
  For Each Data In Array(DataL, DataR)
    Formula = DefFormula
    If i = 0 Then
      LRef = BaseRef(DataL)
      LRefB = LRef & "B"
      RRef = BaseRef(DataR)
      RRefB = RRef & "B:B"
    Else
      LRef = BaseRef(DataR)
      LRefB = LRef & "B"
      RRef = BaseRef(DataL)
      RRefB = RRef & "B:B"
    End If
    Formula = Replace(Formula, "[LRef]", LRef)
    Formula = Replace(Formula, "[RRef]", RRef)
    Formula = Replace(Formula, "[RRefB]", RRefB)
    Formula = Replace(Formula, "[LRefB]", LRefB)
 
    With ThisWorkbook.Sheets(i + 1).Range("A1")
      .CurrentRegion.Clear
      .Resize(Data.Rows.Count, Data.Columns.Count).Formula2Local = Formula
    End With
   
    With Data.FormatConditions
      .Delete
      Set FC = .Add(xlExpression, Formula1:=Formula)
      With FC.Interior
        .Pattern = xlSolid
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.6
      End With
    End With
    i = i + 1
  Next
End Sub

Man kann nun wunderbar die Unterschiede in den beiden Stücklisten sehen, die Teilenummer in Spalte B ist der Schlüssel.

Das Problem ist das die Teile die in der anderen CSV nicht vorkommen nicht eingefärbt werden. Wenn man sich die Formeln im Tool anschaut, dann sieht man das in den Fällen die Zellen ein #NV enthalten. Also war die Idee die Formel in der bedingten Formatierung in ein WENNFEHLER einzuschließen und für diese Fälle eine 1 zurückzugeben.

Tja, das funktioniert leider nicht, jetzt werden generell alle Zeilen eingefärbt. Kann man sehen wenn man  #Const Version = 2 setzt.

Warum hat das WENNFEHLER in der bedingten Formatierung nicht den gewünschten Effekt? Kann das an den dynamic arrays liegen? 
Wenn ja, wo in der Formel müssten überall @ rein damit es funktioniert?

Andreas.

[slowboarder]

Hi
eine Bedingte Formatierung wird aktiv, wenn die Formel für die jeweilige Zelle eine WAHR oder eine Zahl ungleich 0 ergibt.

mit deiner Formel bekommst du immer eine Zahl ungleich 0, entweder die Zeilennummer der Fundstelle das Ergebnis des Vergleich, oder die 1 aus dem Wennfehler.

die richtige Formel, um Zellen zu färben, die nicht in der anderen Liste drin sind, kannst du verwenden:

=ISTFEHLER(VERGLEICH(Suchwert,Suchspalte;0))

oder:

=ZÄHLENWENNS(Suchspalte;Suchwert)=0

Gruß Daniel

[Ralf A]

Hi Andreas,

anbei mal ein anderer Ansatz.

Dateien via PQ eingelesen und die bedingte Formatierung im Sheet mit den meisten Datensätzen auf unterschiedliche Werte beider Tabellen verglichen.


  Vergleich.xlsm (Größe: 34,42 KB / Downloads: 1)