Registriert seit: 14.01.2015
Version(en): 2003
Hi alle zusammen,
hab mein Glück bereits selber schon versucht aber das umbasteln der Formeln klappt halt leider dann doch noch nicht immer wie ich es gern möchte.
ich bräuchte eine Formel, wobei wohl eher ein VBA Code sinnvoller wäre, der mir folgenden Arbeitsschritt ausführt:
in "Tabelle 1"
Spalte: F , L , R, X, AD, AJ, AP, AV, BB, BH, BN und BT
ab Zeile "14" bis "100" nach dem Wert "F" durchsuchen.
Wenn der Wert in einer Zelle vorkommt, die 3 Zellen vor diesem Wert kopieren und in einer Liste in "Tabelle 2" eintragen
Bsp:
in Zelle: "F18" kommt der Wert "F" vor
dann "C, D, E" kopieren und in "Tabelle 2" eintragen.
Alle Zellen die Kopiert werden, egal aus welcher Spalte der Wert "F" stammt. können in "Tabelle 2" einfach untereinander eingetragen werden.
hoff ich habs gut erklärt...
damit Ihr nicht überlegen müsst, welche 3 Zellen vor den Spalten liegen, habe ich Sie euch hier mal aufgelistet:
C D E : F
I J K : L
O P Q : R
U V W : X
AA AB AC : AD
AG AH AI : AJ
AM AN AO : AP
AS AT AU : AV
AY AZ BA : BB
BE BF BG : BH
BK BL BM : BN
BQ BR BS : BT
Tausen dank schonmal an die, die sich der Sache annehmen.
Gruß
Stefan
Registriert seit: 21.12.2017
Version(en): MS 365 Family (6 User x 5 Geräte für jeden) Insider-Beta
A101: f
B101: {=MIN(WENN((REST(SPALTE($F$14:$BT$100);6)=0)*($F$14:$BT$100=A101);SPALTE($F$14:$BT$100)+ZEILE($F$14:$BT$100)%%))}
kein {} eingeben, sondern Formel mit Strg-Umsch-Eingabe abschließen
C101[:E101]: =INDEX($A:$BZ;REST($B101;1)/1%%;$B101-4+SPALTE(A101))
Eine Tabellenformel lässt sich auch in VBA verwenden.
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
04.03.2018, 06:38
(Dieser Beitrag wurde zuletzt bearbeitet: 04.03.2018, 06:38 von schauan.)
Hallöchen,
das könnte man so lösen. Der Ansatz geht davon aus, dass F nur ein mal pro Spalte enthalten ist. Kommt es öfter vor, müsste man den Code erweitern.
Code: Sub F_Kopieren()
'Variablendeklaration
'Array, Variant
Dim arrCols, iFoundRow
'Integer
Dim iCnt%
'Bereich
Dim rngSearch As Range
'Spalten definieren
arrCols = Array("F", "L", "R", "X", "AD", "AJ", "AP", "AV", "BB", "BH", "BN", "BT")
'Schleife ueber alle Spalten
For iCnt = LBound(arrCols) To UBound(arrCols)
'Suchbereich festlegen
Set rngSearch = Range(arrCols(iCnt) & "14:" & arrCols(iCnt) & "100")
'Schauen, obb "F" drin ist
If WorksheetFunction.CountIf(rngSearch, "F") > 0 Then
'Zeile suchen
iFoundRow = rngSearch.Find(What:="F", _
After:=Range(arrCols(iCnt) & "14"), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
'3 Zellen links daneben kopieren
Range(arrCols(iCnt) & iFoundRow).Offset(, -3).Resize(1, 3).Copy
'in Tabelle 2 einfuegen. Beginnt bei leerem Blatt in A2! (kann man auch anders programmieren)
Sheets("Tabelle2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
'Ende Schauen, obb "F" drin ist
End If
'Ende Schleife ueber alle Spalten
Next
End Sub
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Registriert seit: 14.01.2015
Version(en): 2003
04.03.2018, 12:33
(Dieser Beitrag wurde zuletzt bearbeitet: 04.03.2018, 12:33 von lion7123.)
guten morgen ::)
is ja spitze ... da steht ma auf, die sonne scheint endlich mal wieder seit jahren und man hat zwei antworten zu seinem problem :21:
@LCohen ich bin mir sicher dein Ansatz funktioniert, allerdings bin ich mir unsicher wo ich das ganze eingeben soll
alles in den code von Tabelle 1 schreiben oder ein Teil auch in die Zelle ?
@schauan
den Code hab ich in Tabelle 1 geschrieben und wenn ich das ganze ausführen lass funktioniert er.
leider kann "F" auch öfters in einer Spalte vorkommen .. sorry das hab ich dann wohl vergessen zu erwähnen
PS: mir is gerade aufgefallen, das wenn eine der 3 zellen geändert wird, oder das F gelöscht wird sie neu aufgelistet bzw nicht gelöscht werden in Tabelle2.
aber evtl hängt das damit zusammen das F häufiger vorkommen kann.
Registriert seit: 17.04.2014
Version(en): MS Office 365(32)
Hallo Stefan,
kannst es ja mal damit probieren: Sub F_Kopieren_Kuwer()
'Variablendeklaration
Dim lngZeile As Long
Dim rngSpalten As Range
Dim rngFund As Range
Dim strFundadresse As String
'Bereich definieren
Set rngSpalten = Application.Intersect(Rows("14:100"), Range("F1, L1, R1, X1, AD1, AJ1, AP1, AV1, BB1, BH1, BN1, BT1").EntireColumn)
With rngSpalten
Set rngFund = .Find(What:="F", _
After:=.Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFund Is Nothing Then
strFundadresse = rngFund.Address
lngZeile = 2
Sheets("Tabelle2").Cells(lngZeile, 1).Resize(Rows.Count - lngZeile, 3).Value = ""
Do
Sheets("Tabelle2").Cells(lngZeile, 1).Resize(, 3).Value = rngFund.Offset(, -3).Resize(, 3).Value
lngZeile = lngZeile + 1
Set rngFund = .FindNext(rngFund)
Loop While Not rngFund Is Nothing And strFundadresse <> rngFund.Address
Else
MsgBox "Keine Zellen gefunden.", vbInformation
End If
End With
End Sub Gruß Uwe
Folgende(r) 1 Nutzer sagt Danke an Kuwer für diesen Beitrag:1 Nutzer sagt Danke an Kuwer für diesen Beitrag 28
• lion7123
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
Hallo Lion,
da auf der Zieltabelle nicht steht, woher die Daten kommen - außerdem sollen ja alle schön untereinander - wird es fast unmöglich, sie auch wieder zu entfernen. Es sei denn, Du sagst jetzt, dass die Daten in jeder Zeile der verschiedenen Spalten in den 3 Zellen daneben mit 100%iger Sicherheit unterschiedlich sind. Dann könnte man Bei Änderung in einer der Zellen prüfen, ob die zugehörigen Daten dort sind und sie entfernen.
Oder Du schreibst in einer zusätzlichen Spalte die Herkunft ein.
Bei einer reinen Formellösung würde es auch so gehen. Eine Formel muss normalerweise an die Stelle, wo Du das Ergebnis haben willst es sei denn, sie erzeugt ein Zwischenergebnis. Ich habe die Formel von LCohen aber nicht ausprobiert ...
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:1 Nutzer sagt Danke an schauan für diesen Beitrag 28
• lion7123
Registriert seit: 14.01.2015
Version(en): 2003
So Hi ...
sorry in letzter Zeit is so viel los das ich nich dazu gekommen bin hier zu Antworten. Aber ich hab den Code immerhin ausprobieren können und hab ihn soweit ich es rausgefunden habe auch erweiter
1. der Code funzt an sich soweit super
2. nachdem ich deinen nachträglichen Post gelesen habe, hab ich mal paar Testeingaben mehr durchgeführt und dabei ist mir folgendes aufgefallen.
Da das Jahr ja noch ganz Jung ist, gibt es im Moment noch recht wenig Zellen mit dem Wert "F"
wenn ich nun immer wieder einen entferne und das Makro neu laufen lasse, dann verschwinden die Einträge auch in Tabelle2.
NUR das letzte F, sollte es verschwinden wird nicht gelöscht. da kommt die Meldung : keine Zelle gefunden und der Eintrag bleibt bestehen.
Ich denke mal das du dass mit deinem letzten Post gemeint hast oder ?
Und ja ich kann mit ziemlicher Sicherheit sagen das auf alle Fälle 2 der 3 Spalten immer Unterschiedliche Werte haben werden.
Bsp: in Spalte H - wird der Wert "F" gesucht
dann sind die zwei daneben immer unterschiedlich ( G / F )
Evtl. steht in der Spalte E das ein oder andere mal öfters an der gleich Position.
Aber ich habe nun noch eine 4. Spalte hinzugemogelt in der immer Zwei Werte vorkommen aber bei jede Bereichsabfrage unterschiedlich sind. ( ich hoff das war jetzt verständlich :20: )
Ich post mal zur Sicherheit den Code damit ich auch nix kaputt gemacht habe :19:
Code: Sub F_Kopieren_Kuwer()
'Variablendeklaration
Dim lngZeile As Long
Dim rngSpalten As Range
Dim rngFund As Range
Dim strFundadresse As String
'Bereich definieren
Set rngSpalten = Application.Intersect(Rows("14:100"), Range("H1, O1, V1, AC1, AJ1, AQ1, AX1, BE1, BL1, BS1, BZ1, CG1").EntireColumn)
With rngSpalten
Set rngFund = .Find(What:="F", _
After:=.Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFund Is Nothing Then
strFundadresse = rngFund.Address
lngZeile = 4
Sheets("Tabelle2").Cells(lngZeile, 4).Resize(Rows.Count - lngZeile, 7).Value = ""
Do
Sheets("Tabelle2").Cells(lngZeile, 4).Resize(, 4).Value = rngFund.Offset(, -4).Resize(, 4).Value
lngZeile = lngZeile + 1
Set rngFund = .FindNext(rngFund)
Loop While Not rngFund Is Nothing And strFundadresse <> rngFund.Address
Else
MsgBox "Keine Zellen gefunden.", vbInformation
End If
End With
End Sub
IngZeile 4 = die Zeile in der die ergebenisse einträgt
Cell(IngZeile 4 ) = hat aus irgend einem Grund mit der Spalte zu tun so wie ich das gesehen habe
Rows.Count - IngZeile, 7) ich bin mir echt nicht sicher funktioniert irgendwie mit jeder Zahl aber 7 is mit sicherheit falsch :D
und die anderen bedeuten bzw bestimmen die anzahl der Zellen neben dem Wert F die übernommen werden solln oder eben gelöscht werden ?!?! oder
Gruß Stefan - PS lach nich :s :21:
Registriert seit: 14.01.2015
Version(en): 2003
servus nomoi ... hab vorhin leider erst gesehen das ich dich voll übersehen habe kuwer und der code ja eigentlich von dir stammt.
ka warum ich des net gesehn hab. Auf jeden fall wollt ich mich damit kurz entschuldigen. war echt keine absicht.
Gruß Stefan
|