Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1304to1308
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Spalte in anderer Tabelle nach Werten durchsuchen

Spalte in anderer Tabelle nach Werten durchsuchen
20.03.2013 10:18:33
Romina
Hallo,
ich habe mir per VBA eine Datei öffnen lassen und möchte in dieser Datei in der Spalte A nach "Summe" suchen lassen und dann in meiner Ursprungsdatei ausgeben lassen, ob die Werte in den Zeilen, wo er "Summe" findet, =0 sind (Zeilenmax= 2475 und Spaltenmax= Spalte CP).
Kann mir jemand helfen?
Viele Grüße
Romina

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Info an dich Romina
20.03.2013 10:24:43
Matze
Hallo Romina,
du solltest das bisherige Makro einstellen, das wir sehen was dort passiert.
Ebenso brauchen wir die Angabe, "wohin" in deiner Ursprungsdatei.
Soll die komplette Zeile übertragen werden?
Nach "Summe" suchen ist des ein Text oder eine Formel? =Summe(...)
Matze

AW: Info an dich Romina
20.03.2013 10:56:10
Romina
Hallo Matze,
hier mein "Versuch". Einen richtigen Ansatz habe ich nicht...
Er soll nach dem Wort Summe suchen, und wenn er es gefunden hat, soll er alle Werte in dieser Zeile summieren.
Anschließend soll es in die andere Datei in Spalte J. (Es sind unterschiedliche Dateien und unterschiedlich Tabellenblätter). Ich hoffe, es ist verständlicher...
VG
Private Sub Aktualisieren_Click()
Dim Layover As Worksheet
Dim Liege As Worksheet
Dim i As Long
Set Liege = Worksheet("Tabelle1")
Set Layover = Worksheet("Tabelle2")
With Liege
If .Cells(i, 1) = "Summe" Then
"=SUM('DATEI1.xlsm]Tabelle1'!R10C2:R10C94)"
Layover.Cells(65536, 10).End(xlUp).Offset(1, 0).Select
Next i
End With
End Sub

Anzeige
AW: Info an dich Romina
20.03.2013 11:09:04
Klaus
Hallo Romina,
in deinem ersten Post schreibst du noch "per VBA eine Datei öffnen lassen und möchte in dieser". In dem o.g. Makro wird aber keine Datei geöffnet, sondern nur mit Tabellenblättern hantiert. Allerdings ist da noch einiges anderes seltsam, und du schreibst selbst dass es noch nicht der richtige Ansatz ist ...
Um dir zu helfen, müssen wir dich verstehen. Bitte beschreibe in Prosa, was du möchstest. Ich verstehe dich jetzt so:
- Ich bin in einer Datei Namens "Beispiel.xlsx"
- ich möchte per VBA eine andere Datei öffnen,
- deren Name und Pfad mir bekannt sind (woher?)
- in dieser neuen Datei die Spalte A nach dem Wort "Summe" durchsuchen
- bei gefundener Summe, den Wert von Spalte B bis CP in der aktuellen Zeile ermitteln
- die externe Datei wieder schließen
- in "Beispiel.xlsx" schreiben, ob die ermittelte Summe 0 war oder größer
Hab ich das richtig?
Grüße,
Klaus M.vdT.

Anzeige
AW: Info an dich Romina
20.03.2013 11:25:50
Romina
Ich habe öffne die Datei "Beispiel.xlsm" und über einen Button öffne ich eine Datendatei "Datei1", aus der ich die Informationen beziehen möchte (soweit funktioniert es auch). Ist in "Datei1+Tabelle1" in der 1.Spalte das Wort "Summe", soll in dieser Zeile, B bis CP, die Summe gezogen werden und anschließend in "Beispiel+Tabelle2" in der nächsten freien Zelle der Spalte J ausgegeben werden. Schließen ist unwichtig, per Hand reicht vollkommen aus.
Also hattest du es im Grunde richtig Klaus!
Grüße zurück!
Romina

AW: Info an dich Romina
20.03.2013 11:52:03
Klaus
Hi,
das sollte es tuen. Ich habe mich akribisch an deine Vorgaben (Tabellennamen, Spalten) aus dem Beitrag gehalten. Wenn in deiner Masterdatei irgendwas anders heisst, musst du entsprechend umbenennen. Ich gehe davon aus, dass in Zelle A1 (also in Zeile 1) niemals eine Summe stehen wird!
Sub CheckeSummeInExternemFIle()
Dim wkbOld As Workbook
Dim wkbNew As Workbook
Dim lRow As Long
Dim lWert As Double
'aktuelle Datei merken, neue Datei öffnen
Set wkbOld = ActiveWorkbook
Call FileCheckOpen("C:\TestTmp", "Datei1.xlsm") 'ANPASSEN
'neue Datei merken
Set wkbNew = ActiveWorkbook
Sheets("Tabelle1").Activate                     'ANPASSEN
With ActiveSheet
'letzte Zeile
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'Autofilter nutzen, um das Wort "Summe" zu finden
'geht VIEL schneller als eine Schleife über jede Zelle!
If .AutoFilterMode Then .Cells.AutoFilter
.Range("A1").Value = "TEMP" 'Autofilter braucht eine gefüllte erste Zelle
.Range(.Cells(1, 1), .Cells(lRow, 1)).AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:="=Summe"
'Summe ermitteln, falls es eine gibt (sonst null)
lWert = Application.WorksheetFunction.Sum(.Range("B1:CP" & lRow).SpecialCells( _
xlCellTypeVisible))
End With
'Datei schließen ohne speichern
wkbNew.Close False
'alte Datei aktivieren
wkbOld.Activate
Sheets("Tabelle2").Activate
With ActiveSheet
'"Beispiel+Tabelle2" in der nächsten freien Zelle der Spalte J ausgegeben werden
lRow = .Cells(.Rows.Count, 10).End(xlUp).Row + 1
.Range("J" & lRow).Value = lWert
End With
End Sub
'*********************************************************************************************** _
'* Module to open needed files. Checks if Files are open or not.
'* If file is already open, do nothing - else open it
'* stolen from: https://www.herber.de/mailing/Pruefen_ob_Arbeitsmappe_geoeffnet_und_wenn_nein_oeffnen.htm
'* modified by Klaus MvdT / 16.NOV.2012
'*********************************************************************************************** _
'Example:
'Call FileCheckOpen("C:\TMP", "Filename.xls")
'path and filename can be RANGE from excelsheet
Sub FileCheckOpen(sPath As String, sFile As String)
sPath = sPath & "/" & sFile
If WkbExists(sFile) = False Then
If Dir(sPath) = "" Then
MsgBox "File " & sPath & " not found!"
Else
Workbooks.Open sPath, UpdateLinks:=False
End If
Else
Workbooks(sFile).Activate
End If
End Sub
Private Function WkbExists(sFile As String) As Boolean
Dim wkb As Object
On Error Resume Next
Set wkb = Workbooks(sFile)
If Not wkb Is Nothing Then
WkbExists = True
End If
On Error GoTo 0
End Function

Grüße,
Klaus M.vdT.

Anzeige
AW: Info an dich Romina
20.03.2013 12:47:27
Romina
Hallo Klaus,
ich habe als String den Pfad definiert und angegeben und möchte nur einen Ordner öffnen, aus dem man eine Datei öffnet (Name kann sich ändern). Wie muss ich da den call-Befehl ändern?
...
Dim Pfad As String
Pfad = "C:\TestTmp"
'aktuelle Datei merken, neue Datei öffnen
Set wkbOld = ActiveWorkbook
Call FileCheckOpen(Pfad, ?) 'ANPASSEN
...
Danke dir! VG
Romina

AW: Info an dich Romina
20.03.2013 13:01:13
Romina
bzw. kann ich nicht irgendwie direkt die Dateien und Tabellen deklarieren?
Geht es nur über Activeworkbook?

AW: Info an dich Romina
20.03.2013 13:34:05
Klaus
und möchte nur einen Ordner öffnen, aus dem man eine Datei öffnet (Name kann sich ändern). Wie muss ich da den call-Befehl ändern?
?
Willst du PER HAND die Datei aus dem Ordner öffnen oder per Makro?
Du schriebst selbst:
und über einen Button öffne ich eine Datendatei "Datei1",
dass die Datei nicht per Hand, sondern im VBA geöffnet werden soll. Da du auf meine Frage "woher"
- deren Name und Pfad mir bekannt sind (woher?)
nicht eingeangen bist nahm ich an, ich kann das fest in den Code schreiben.
bzw. kann ich nicht irgendwie direkt die Dateien und Tabellen deklarieren?
Ja. Sind sie ja. Hier:
Call FileCheckOpen("C:\TestTmp", "Datei1.xls") 'ANPASSEN
steht doch direkt "C:\TestTmp\Datei1.xls" ....
  • Geht es nur über Activeworkbook?

  • Andersrum gefragt: Warum denn NICHT über active workbook? Wenn ich die Datei öffne (und das muss ich), dann ist sie ja direkt danach aktiv. Jeder andere Weg währ doch nur doppelt umständlich.
    Romina,
    sei mir nicht böse, aber so bringt das nichts. Ich hab ja vollstes Verständniss für jeden der kein VBA oder Excel kann, aber wenn du nicht in Prosa erklären kannst was du brauchst dann kann ich dir auch nicht helfen. Der gelistete Code erfüllt die gestellte Aufgabe mit Bravour.
    Grüße,
    Klaus M.vdT.

    Anzeige
    AW: Info an dich Romina
    20.03.2013 13:49:55
    Romina
    Mein Problem ist, dass ich den Ordner (Pfad ist bekannt, Dateiname aber nicht) per VBA öffnen lasse in dem die Dateien sind, da sich die Namen immer wieder ändern. Die Datei wird dann immer am Anfang der Abfrage neu erfragt.
    Ist das verständlicher?
    VG

    AW: Info an dich Romina
    20.03.2013 13:52:57
    Romina
    Ich habe es jetzt mal so versucht:
    ...
    Dim Layover As Workbook
    Dim Lage As Workbook
    Dim Pfad As String
    Set Layover = ActiveWorkbook
    Pfad = "C:\TestTmp""
    Shell "Explorer.exe " & Pfad, vbNormalFocus
    Set Lage = ActiveWorkbook
    Sheets("Tabelle1").Activate
    ....

    AW: Info an dich Romina
    20.03.2013 14:49:01
    Klaus
    Hi,
    ich weiss absolut nicht worauf du hinaus möchtest. Statt zu raten, was dein Code bewirken soll, habe ich den Windows FilePicker in das Makro eingebaut.
    ChDir "C:\TestTMP"
    Hier deinen Pfad angeben!
    Option Explicit
    Sub CheckeSummeInExternemFIle()
    On Error GoTo hell
    Dim wkbOld As Workbook
    Dim wkbNew As Workbook
    Dim lRow As Long
    Dim lWert As Double
    'aktuelle Datei merken, neue Datei öffnen
    Set wkbOld = ActiveWorkbook
    ChDir "C:\TestTMP"
    Call FileCheckOpen(GetFile) 'ANPASSEN
    'neue Datei merken
    Set wkbNew = ActiveWorkbook
    Sheets("Tabelle1").Activate                     'ANPASSEN
    With ActiveSheet
    'letzte Zeile
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    'Autofilter nutzen, um das Wort "Summe" zu finden
    'geht VIEL schneller als eine Schleife über jede Zelle!
    If .AutoFilterMode Then .Cells.AutoFilter
    .Range("A1").Value = "TEMP" 'Autofilter braucht eine gefüllte erste Zelle
    .Range(.Cells(1, 1), .Cells(lRow, 1)).AutoFilter
    .Range("A1").AutoFilter Field:=1, Criteria1:="=Summe"
    'Summe ermitteln, falls es eine gibt (sonst null)
    lWert = Application.WorksheetFunction.Sum(.Range("B1:CP" & lRow).SpecialCells( _
    xlCellTypeVisible))
    End With
    'Datei schließen ohne speichern
    wkbNew.Close False
    'alte Datei aktivieren
    wkbOld.Activate
    Sheets("Tabelle2").Activate
    With ActiveSheet
    '"Beispiel+Tabelle2" in der nächsten freien Zelle der Spalte J ausgegeben werden
    lRow = .Cells(.Rows.Count, 10).End(xlUp).Row + 1
    .Range("J" & lRow).Value = lWert
    End With
    GoTo heaven
    hell:
    MsgBox ("Abgebrochen!")
    heaven:
    End Sub
    Function GetFile()
    Dim varDatei As Variant
    varDatei = Application.GetOpenFilename()
    If varDatei = False Then
    MsgBox "Der Benutzer hat abgebrochen.", vbInformation
    Else
    GetFile = varDatei
    End If
    End Function
    '*********************************************************************************************** _
    _
    '* Module to open needed files. Checks if Files are open or not.
    '* If file is already open, do nothing - else open it
    '* stolen from: https://www.herber.de/mailing/Pruefen_ob_Arbeitsmappe_geoeffnet_und_wenn_nein_oeffnen.htm
    '* modified by Klaus MvdT / 16.NOV.2012
    '*********************************************************************************************** _
    _
    'Example:
    'Call FileCheckOpen("C:\TMP", "Filename.xls")
    'path and filename can be RANGE from excelsheet
    Sub FileCheckOpen(sPath As String)
    'sPath = sPath & "/" & sFile
    If WkbExists(sPath) = False Then
    If Dir(sPath) = "" Then
    MsgBox "File " & sPath & " not found!"
    Else
    Workbooks.Open sPath, UpdateLinks:=False
    End If
    Else
    Workbooks(sPath).Activate
    End If
    End Sub
    Private Function WkbExists(sFile As String) As Boolean
    Dim wkb As Object
    On Error Resume Next
    Set wkb = Workbooks(sFile)
    If Not wkb Is Nothing Then
    WkbExists = True
    End If
    On Error GoTo 0
    End Function
    
    Grüße,
    Klaus M.vdT.
    Anzeige

    384 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige