Registriert seit: 04.03.2015
Version(en): 2000 + meist 2010
Och, mal ein Hugo trinkt sich ganz nett ;)
Registriert seit: 14.04.2014
Version(en): 2003, 2007
08.02.2017, 12:07
(Dieser Beitrag wurde zuletzt bearbeitet: 08.02.2017, 12:07 von 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
Gruß Atilla
Registriert seit: 08.02.2017
Version(en): 2016
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"))
Excel Version 2016
Registriert seit: 13.04.2014
Version(en): 365, 2019
Hi,
ich bin raus!
Gruß
Edgar
Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Registriert seit: 08.02.2017
Version(en): 2016
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.
Excel Version 2016
Registriert seit: 14.04.2014
Version(en): 2003, 2007
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.
Gruß Atilla
Registriert seit: 08.02.2017
Version(en): 2016
08.02.2017, 13:20
(Dieser Beitrag wurde zuletzt bearbeitet: 08.02.2017, 13:20 von dertommy.)
Hallo!
Danke für deinen Code. Schaut einmal sehr gut aus. Ich werde ihn nun komplett in meinen bestehende Code einpflegen.
Excel Version 2016
Registriert seit: 08.02.2017
Version(en): 2016
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
Excel Version 2016
|