Zelle auslesen und in Formel ausgeben

[lupo1]

Och, mal ein Hugo trinkt sich ganz nett ;)

[atilla]

Hallo,

du weißt, dass bisher in M folgende Formel reingeschrieben wurde:

"=DATEDIF(VLOOKUP(""" & strFind & """,Panels!R[-107]C[-11]:R[88]C[-4],6,FALSE),TODAY(),""M"")/(COUNTIFS(Anweisungen!C[-12],""" & strFind & """,Anweisungen!C[-8],""ausgezahlt""))"

Soll statt dieser jetzt die andere von mir gezeigte dort stehen?
Wenn ja, dann ersetze obige mit meiner, das war es..

@Edgar

Der Te schreibt in die erste freie Zelle in Spalte A der Tabelle "Anweisung" "Hugo" oder was auch immer rein.
Dann springt das Change Ereignis an.

Es wird in einer anderen tabelle geschaut, ob dort ein "Hugo" vorhanden.
Wenn Ja, dann das Datum aus Spalte G festhalten.

Dann in Tabelle Top30 in einer Formel diesen Wert verwerten. Zum Breispiel in der von mir gezeigten Formel

[dertommy]

Du kannst statt "hugo" einfach "neuesPanel" nehmen. Es darf auf jeden Fall kein Wert sein, der sich bereits im Tabellenblatt "Top30" befindet. Denn dorthin soll ja nur die Formel neu reingeschrieben werden, wenn es den Wert noch nicht gibt. An dieser Logik soll sich nix ändern, wie mein bestehender Code derzeit aussagt.

=DATEDIF("22.09.2007";HEUTE();"M")/(ZÄHLENWENNS(Anweisungen!A:A;"neuesPanel";Anweisungen!E:E;"ausgezahlt"))

[BoskoBiati]

Hi,

ich bin raus!

[dertommy]

Hallo atilla!

Leider kommt mit deinem Code beim Debuggen ein Fehler. Es soll folgende Codezeile von mir verändert werden:

Code:

.Cells(q, 13).FormulaR1C1 = "=DATEDIF(VLOOKUP(""" & strFind & """,Panels!R[-107]C[-11]:R[88]C[-4],6,FALSE),TODAY(),""M"")/(COUNTIFS(Anweisungen!C[-12],""" & strFind & """,Anweisungen!C[-8],""ausgezahlt""))"

Hier wird ja die Formel "Sverweis" verwendet. Diese will ich eben nicht mehr in der Formel haben, sondern eben fix den Wert aus Spalte G stehen haben.
Es sollte dann lediglich folgende Formel stehen:

=DATEDIF("22.09.2007";HEUTE();"M")/(ZÄHLENWENNS(Anweisungen!A:A;"neuerPanel";Anweisungen!E:E;"ausgezahlt"))

Danke dir jetzt schon.

[atilla]

Hallo,

da Dein Change Ereignis mir nicht geheuer ist, habe ich das mal in eine Routine gepackt.
Du kannst diese aber auch aus dem Change Ereignis heraus aufrufen, mit dem Befehl

Code:

Sub finde_hugo()
Dim strgS As String
Dim rngF As Range
Dim DaDate As Date

With ThisWorkbook.Worksheets("Anweisungen")
 strgS = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1) 'letzter Eintarg aus Spalte A der Tabelle Anweisung
End With

With ThisWorkbook.Worksheets("Panels")
 Set rngF = .Columns(2).Find(strgS, LookAt:=xlPart)
 If Not rngF Is Nothing Then
   DaDate = .Cells(rngF.Row, 7)
 End If
End With

'Formel wird in die erste freie Zelle der Spalte m in Top30 geschrieben
With ThisWorkbook.Worksheets("Top30")
.Cells(.Cells(.Rows.Count, 13).End(xlUp).Row + 1, 13).Formula = "DATEDIF(DateValue(""" & DateValue(DaDate) & """),TODAY()," & """M""" & ")/(COUNTIFS(Anweisungen!A:A,""" & strgS & """,Anweisungen!E:E," & """ausgezahlt""" & "))"
End With

End Sub

Die Formel müsste jetzt richtig sein.

Bin dann auch mal raus.

[dertommy]

Hallo!

Danke für deinen Code. Schaut einmal sehr gut aus. Ich werde ihn nun komplett in meinen bestehende Code einpflegen.

[dertommy]

Danke nochmals für deine Bemühungen. Hier nun der entgültige Code, welcher auch so funktioniert, wie ich es will:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) 'Wenn etwas geändert wird

If Target.Column = 1 Then
          If Not IsEmpty(Cells(Target.Row, 1)) Then
                   Cells(Target.Row, 3).Value = Date
                   
                   Dim strgS2 As String
                   Dim rngF2 As Range
                   Dim Art As String

                   With ThisWorkbook.Worksheets("Anweisungen")
                       strgS2 = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1) 'letzter Eintarg aus Spalte A der Tabelle Anweisung
                   End With

                   With ThisWorkbook.Worksheets("Panels")
                       Set rngF2 = .Columns(2).Find(strgS2, LookAt:=xlPart)
                       If Not rngF2 Is Nothing Then
                           Art = .Cells(rngF2.Row, 3)
                       End If
                   End With

                   'Formel wird in die erste freie Zelle der Spalte m in Top30 geschrieben
                   With ThisWorkbook.Worksheets("Anweisungen")
                       .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row + 1, 2).Formula = " " & Art & ""
                   End With
                   Exit Sub
           End If
 End If
         
         
   If Target.Column = 6 Then 'in Spalte E
       
       If ActiveSheet.Cells(Target.Row, 1).Value <> vbNullString Then 'und Spalte A nicht leer ist
           strFind = ActiveSheet.Cells(Target.Row, 1).Value
                     
           Set rngFind = ThisWorkbook.Worksheets("Top30").Columns(7).Find(What:=strFind, LookAt:=xlPart) 'suche Wert aus Spalte A in Top30 in Spalte B
           If Not rngFind Is Nothing Then 'Wenn Wert existiert dann
               ThisWorkbook.Worksheets("Top30").Activate
               Top30alleanzeigen
               Top30anzeigen
               ThisWorkbook.Worksheets("Anweisungen").Activate
           Else 'sonst

               With ThisWorkbook.Worksheets("Top30")
                   q = .Cells(6, 5).CurrentRegion.Rows.Count + 6
                   .Cells(q, 1).FormulaR1C1 = "=RANK(RC[2],C[2])"
                   .Cells(q, 2).FormulaR1C1 = "=""" & strFind & "  (""&COUNTIFS(Anweisungen!C[-1],""" & strFind & """,Anweisungen!C[3],""ausgezahlt"")& ""x)"""
                   .Cells(q, 3).FormulaR1C1 = "=SUMIFS(Anweisungen!C[1],Anweisungen!C[-2],""" & strFind & """,Anweisungen!C[2],""ausgezahlt"")"
                   .Cells(q, 4).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-2],1,0)),""nicht aktiv"",""aktiv"")"
                   
                   .Cells(q, 6).FormulaR1C1 = "=RANK(RC[2],C[2])"
                   .Cells(q, 7).FormulaR1C1 = "=""" & strFind & "  (€ ""&TEXT(SUMIFS(Anweisungen!C[-3],Anweisungen!C[-6],""" & strFind & """,Anweisungen!C[-2],""ausgezahlt""),""#.##0,00"") & "")"""
                   .Cells(q, 8).FormulaR1C1 = "=COUNTIFS(Anweisungen!C[-7],""" & strFind & """,Anweisungen!C[-3],""ausgezahlt"")"
                   .Cells(q, 9).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-7],1,0)),""nicht aktiv"",""aktiv"")"
                   
                   .Cells(q, 11).FormulaR1C1 = "=RANK(RC[2],C[2])"
                   .Cells(q, 12).FormulaR1C1 = "=""" & strFind & "  (€ ""&TEXT(SUMIFS(Anweisungen!C[-8],Anweisungen!C[-11],""" & strFind & """,Anweisungen!C[-7],""ausgezahlt""),""#.##0,00"") & "")"""
                   
                   Dim strgS As String
                   Dim rngF As Range

                   With ThisWorkbook.Worksheets("Anweisungen")
                       strgS = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1) 'letzter Eintarg aus Spalte A der Tabelle Anweisung
                   End With

                   With ThisWorkbook.Worksheets("Panels")
                       Set rngF = .Columns(2).Find(strgS, LookAt:=xlPart)
                       If Not rngF Is Nothing Then
                           DaDate = .Cells(rngF.Row, 7)
                       End If
                   End With

                   'Formel wird in die erste freie Zelle der Spalte m in Top30 geschrieben
                   With ThisWorkbook.Worksheets("Top30")
                   .Cells(.Cells(.Rows.Count, 13).End(xlUp).Row + 1, 13).Formula = "=DATEDIF(DateValue(""" & DateValue(DaDate) & """),TODAY()," & """M""" & ")/(COUNTIFS(Anweisungen!A:A,""" & strgS & """,Anweisungen!E:E," & """ausgezahlt""" & "))"
                   End With
                   
                  .Cells(q, 14).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(IFERROR(LEFT(RC[-2],SEARCH("" "",RC[-2],1) -1),RC[-2]),Panels!C[-12],1,0)),""nicht aktiv"",""aktiv"")"
                   
                   ThisWorkbook.Worksheets("Top30").Activate
                   Top30alleanzeigen
                   Top30anzeigen
                   ThisWorkbook.Worksheets("Anweisungen").Activate
               End With
           End If
           
       End If
   
   With ThisWorkbook.Worksheets("Anweisungen")
     If .Cells(Target.Row, 6) = "j" Then
          .Cells(Target.Row, 7) = Date
     End If
End With
   
   End If

End Sub