Office: Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

xlarge · 9. März 2019

Liebe Excelprofis,

ich bekommen eine Tabelle (in Musterdatei "Tabelle1") und möchte nun die Daten in verschiedene Tabellen ("Huber, Gruber, Bauer, usw.) kopieren (insgesamt 9).

In diesen Tabellen habe ich Bereiche benannt zB: "VBA_123456". Nun möchte ich alle Tabellen durchsuchen nach einem Bereich der sich aus VBA_ und der Nummer aus Tabelle1 Spalte A zusammensetzt.
Wird der Bereich gefunden sollte er nach den Typen in Tabelle1/Spalte B durchsucht werden.
Wenn eine Übereinstimmung gefunden wird sollen die Spalten C bis N der Tabelle 1 in den Bereich rechts neben der Fundstelle kopiert werden.

Ich probiere schon länger rum. Aber meine VBA-Kenntnisse sind leider sehr gering.
Vielleicht könnt ihr mir helfen. Vielen Dank.
Gruß Xlarge

:)

MisterBurns · 11. März 2019

Wieso nicht einfach die Nummer auch in die Tabellen "Huber" usw. einfügen, dann kannst du das ganz simpel über einen Verweis machen. Wozu VBA?

xlarge · 11. März 2019

Hallo Berni,
das würde nur gehen wenn die Verweise in den Tabellen Huber, etc. stehen würden. Die Tabelle1 bekomme ich jeden Monat neu und müsste daher die Verweise jedesmal neu eintragen.

cysu11 · 11. März 2019

Nicht wenn du nur die Inahalte der neuen Tabelle einfach reinkopierst!

xlarge · 11. März 2019

ich möchte es aber lieber per VBA machen, da ich es dann vielleicht in abgewandelter Form auch bei anderen Tabellen hinkriege.
Ein Anfang wäre schon einmal Finden des benannten Bereiches und durchsuchen nach Typ.
Vielen Dank.
Gruß Xlarge

EarlFred · 11. März 2019

Zum Finden des Bereichs:
Code:

Option Explicit

Sub test()
Dim rBereich As Range
Dim strBereichsname As String

strBereichsname = "VBA_" & Worksheets("Tabelle1").Range("A6").Value

Set rBereich = findeBereich(strBereichsname)
If Not rBereich Is Nothing Then
Call MsgBox(prompt:=rBereich.Address(external:=True))
Else
Call MsgBox(prompt:="Bereich " & strBereichsname & " wurde in der Mappe nicht gefunden!")
End If
End Sub

Function findeBereich(ByRef Bereichsname As String) As Range
On Error Resume Next
Set findeBereich = ThisWorkbook.Names(Bereichsname).RefersToRange
On Error GoTo 0
End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Der Rest ist dann ja kleines VBA-1x1, für einen alten Hasen wie Dich also sicher kein Problem.

xlarge · 11. März 2019

Danke Earlfred,

habe deinen Code eingebaut. Finde jetzt die jeweiligen Bereiche und kann sie auch durchsuchen. Aber ich bekomme es nicht hin die Zelle rechts neben der Fundstelle anzusprechen um dort etwas einzukopieren.

Es wäre schön wenn das Forum hier ebenfalls helfen könnte.
Gruß xlarge

EarlFred · 11. März 2019

Schau Dir mal die Methoden Offset() und ggf. auch Resize() des Range-Objektes an. Damit bekommst Du einen Versatz bzw. eine Bereichsgrößenänderung hin.

xlarge · 12. März 2019

Hallo Forum,

habe es versucht mit:
Code:

Set ZR = ZR.Cells.Offset(0, 1).Select ' um eine Zelle nach rechts zu verschieben
Set ZR = Range(ZR).Resize(0, 12).Select 'um den Bereich auf 12 Spalten auszubreiten.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

aber es kommt die Fehlermeldung "Anwendungs- ....."

RZ ist die Fundstelle der vorangegangenen Suche. (Beispiel "AM16"

xlarge

xlarge · 14. März 2019

Liebes Forum,

ich habe es nun so versucht:
Code:

Set ZR = ZR.Cells.Find(strLand, LookIn:=xlValues, LookAt:=xlWhole)
If Not ZR Is Nothing Then
MsgBox "gefunden = " & strLand & " in Zelle = " & ZR.Address
Dim Zeile As Long
Dim Spalte As String
Zeile = ZR.Row
Spalte = ZR.Columns.Column + 1

Set ZR = ZR.Range(Spalte & Zeile, Spalte + 11 & Zeile)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Bei der roten Zeile kommt die Fehlermeldung "Anwendungs- und Objektorientierter Fehler"

Was ist an der Syntax falsch? Ich habe "Spalte" auch als Long versucht = gleicher Fehler.

Gruß xlarge

Office: Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen

Benannten Bereich in allen Tabellen einer Arbeitsmappe finden und durchsuchen - Similar Threads - Benannten Bereich Tabellen

SVerweis mit wechselnden benannten Bereich

Zellbezug auf benannten Bereich

Suchen von benannten Bereichen

Erstellen eines benannten Bereiches aus ausgewählten Zellen in einem Arbeitsblatt

Kann Access benannte Bereiche mit dynamischer Größenanpassung nicht verknüpfen?

Adressieren von Bereichen in VBA

Feststellen, ob Zelle in benanntem Bereich?