|
Der Sinn der Formel erschließt sich mir immer noch nicht so ganz, was aber vielleicht nur am gewählten Beispiel liegt.
Wie auch immer:
'im Klassenmodul von Tabelle2
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const C_FORMULA As String = "=IF(%CELL%=%CELL_VALUE%,%TRUE_PART%,%FALSE_PART%)"
Dim rngResult As Excel.Range
Set rngResult = Tabelle1.Columns("A").Find(Target.Cells(1, 1).Value, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchByte:=False)
If Not rngResult Is Nothing Then
Set rngResult = rngResult.Offset(, 3) 'A + 3 columns -> D
Else
Exit Sub
End If
Dim rngCell As Excel.Range
Dim strFormula As String
With Tabelle3
Set rngCell = .Cells(.Rows.Count, "A").End(xlUp)
If Trim$(rngCell.Value) <> "" Then Set rngCell = rngCell.Offset(1)
End With
strFormula = Replace$(C_FORMULA, "%CELL%", "B1", Compare:=vbTextCompare)
strFormula = Replace$(strFormula, "%CELL_VALUE%", IIf(IsNumeric(rngResult.Value), rngResult.Value, """" & rngResult.Value & """"), Compare:=vbTextCompare)
strFormula = Replace$(strFormula, "%TRUE_PART%", """nix""", Compare:=vbTextCompare)
strFormula = Replace$(strFormula, "%FALSE_PART%", """sonst nix""", Compare:=vbTextCompare)
rngCell.Formula = strFormula
End Sub
Gruß
|