Variable über Find definieren

Forumthread: Variable über Find definieren

19.06.2019 09:31:34

_future

Guten Morgen liebe Excelisten,
ich scheitere mal wieder an einem VBA-Problem und hoffe inständig auf eure Unterstützung. Da ich mir VBA quasi per 'Learning by doing' aneigne, bitte ich um eure Nachsicht.
Ich möchte innerhalb eines Makros in einer Tabelle aus zwei Spalten nach einer alten Bezeichnung (in Spalte B) suchen und das Kürzel in der Zelle daneben (in Spalte A) in eine variable übergeben. Die alte Bezeichnung wird über eine Combobox definiert. Mein Problem ist, dass ich es nicht schaffe den Wert daneben in eine Variable zu übergeben (fett markierte Zeile).
Danach soll im nächsten Tabellenblatt eine Replace Funktion angewendet werden - diese funktioniert aber bereits unter anderen Umständen.
Es geht um die folgende Passage:
Dim AlteBezeichnung As String
Dim AltesKürzel As String
Dim NeuesKürzel As String
AlteBezeichnung = ComboBox1.Value
NeuesKürzel = ActiveSheet.Cells(last, 1).Value
AltesKürzel = ActiveSheet.Cells.Find(AlteBezeichnung ).Cells(2, 1).Value
Sheets(ActiveSheet.Index + 1).Activate
ActiveSheet.Range("B:B").Replace What:=AltesKürzel , Replacement:=NeuesKürzel , LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets(ActiveSheet.Index - 1).Activate
Ich freue mich wirklich über jeden Tipp oder Hinweis!

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variable über Find definieren

19.06.2019 09:34:05

Werner

Hallo,

AltesKürzel = ActiveSheet.Cells.Find(AlteBezeichnung ).Cells(2, 1).Offset(,-1).Value

Gruß Werner

AW: Variable über Find definieren

19.06.2019 09:44:17

_future

Hallo Werner,
besten Dank - es funktioniert!
Könntest du mir erklären was mit "Offset" passiert?

AW: Variable über Find definieren

19.06.2019 10:14:49

Werner

Hallo,
hier mal dein Code angepasst.
1. die Variable last ist nicht deklariert und es ist ihr auch kein Wert zugewiesen
2. sollte man bei Find immer die Parameter mit angeben.
-Warum? Excel speichert die Parameter der letzten Suche. Ohne Angabe der Parameter werden die von Excel die gespeicherten Parameter genommen. Wenn du aber bei der letzten Suche z.B. in Formeln gesucht hast, jetzt aber in Werten suchst, dann stimmt dein Suchergebnis nicht, weil du die Parameter nicht angegeben hast.
3. Bei Find würde ich das Suchergebnis immer per Set in einer Range-Variablen ausgeben lassen.
Warum? Lass deinen Code mal laufen und lösch vorher mal in der Tabelle den Suchbegriff, so dass die Suche ins Leere läuft. -dann knallts- Das kann man mit der Range-Variablen einfach abfangen.
4. Suchst du mit Cells im ganzen Blatt, du willst doch aber nur in der Spalte B suchen
5. Das Activat habe ich raus geschmissen, braucht kein Mensch und kostet nur Zeit

Public Sub aaa()
Dim AlteBezeichnung As String, AltesKürzel As String
Dim NeuesKürzel As String, raFund As Range
AlteBezeichnung = ComboBox1.Value
NeuesKürzel = ActiveSheet.Cells(last, 1).Value
Set raFund = ActiveSheet.Columns(2).Find(AlteBezeichnung, LookIn:=xlValues, LookAt:=xlWhole)
If Not raFund Is Nothing Then
AltesKürzel = raFund.Offset(, -1)
Else
MsgBox "Suchbegriff nicht vorhanden."
End If
With Sheets(ActiveSheet.Index + 1)
.Range("B:B").Replace What:=AltesKürzel, Replacement:=NeuesKürzel, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
Set raFund = Nothing
End Sub

Und jetzt zum Offset:
Das ist ein Versatz, ausgehend von der ausgewählten/angesporochenen Zelle.
Bei obigem Code ist der Fund in der Addresse raFund
Ein Offset geht von dieser Zelle aus, der erste Wert in der Klammer des Offset ist der Zeilenversatz, der zweite Wert in der Klammer ist der Spaltenversatz
Positive Werte sind bei Zeilenversatz nach unten, negative nach oben
Beim Spaltenversatz sind positive Werte nach rechts, negative Werte nach links
Beispiel:
Range("D5").Offset(1,0) = D6
Range("D5").Offset(-1,0) = D4
Range("D5").Offset(0,1) = E5
Range("D5").Offset(0,-1) = C5
lässt sich natürlich auch kombinieren
Range("D5").Offset(1,1) = E6
Gruß Werner

AW: Variable über Find definieren

19.06.2019 10:24:40

_future

Hallo Werner,
Danke, Danke, Danke!!!
Grüße
_future

Gerne u. Danke für die Rückmeldung. o.w.T.

19.06.2019 10:34:11

Werner

Infobox / Tutorial

Variable über Find definieren in Excel VBA

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Deklaration der Variablen: Beginne mit der Deklaration deiner Variablen. Für das Suchen und Ersetzen benötigst du mindestens drei Variablen:
```
Dim AlteBezeichnung As String
Dim AltesKürzel As String
Dim NeuesKürzel As String
Dim raFund As Range
```
Zuweisung der Werte: Setze AlteBezeichnung von der Combobox und NeuesKürzel von der aktiven Zelle:
```
AlteBezeichnung = ComboBox1.Value
NeuesKürzel = ActiveSheet.Cells(last, 1).Value
```
Suchen mit Find: Verwende die Find-Methode, um in der zweiten Spalte nach der alten Bezeichnung zu suchen. Stelle sicher, dass du die Parameter angibst:
```
Set raFund = ActiveSheet.Columns(2).Find(AlteBezeichnung, LookIn:=xlValues, LookAt:=xlWhole)
```

Überprüfen des Suchergebnisses: Prüfe, ob die Find-Methode ein Ergebnis zurückgegeben hat:

If Not raFund Is Nothing Then
    AltesKürzel = raFund.Offset(, -1).Value
Else
    MsgBox "Suchbegriff nicht vorhanden."
End If

Ersetzen der Werte: Nutze die Replace-Methode, um die alten Kürzel durch die neuen Kürzel zu ersetzen:

With Sheets(ActiveSheet.Index + 1)
    .Range("B:B").Replace What:=AltesKürzel, Replacement:=NeuesKürzel, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With

Häufige Fehler und Lösungen

Variable nicht deklariert: Stelle sicher, dass alle Variablen (wie last) deklariert und initialisiert sind.
Suchbegriff nicht vorhanden: Wenn der Suchbegriff nicht gefunden wird, zeigt Find nichts an. Verwende eine If-Bedingung, um dies abzufangen.
Keine Parameter bei Find angegeben: Vergiss nicht, die Parameter wie LookIn und LookAt anzugeben, um ungenaue Ergebnisse zu vermeiden.

Alternative Methoden

Loop durch Zellen: Anstelle von Find kannst du auch eine Schleife verwenden, um durch jede Zelle in der Spalte zu iterieren und nach dem Suchbegriff zu suchen.

For Each cell In ActiveSheet.Columns(2).Cells
    If cell.Value = AlteBezeichnung Then
        AltesKürzel = cell.Offset(, -1).Value
        Exit For
    End If
Next cell

VBA-Funktionen: Du kannst auch eigene Funktionen erstellen, um die Suche flexibler zu gestalten, z.B. durch Anpassung der Suchparameter.

Praktische Beispiele

Ein Beispiel für das vollständige Makro:

Public Sub ErsetzeKuerzel()
    Dim AlteBezeichnung As String, AltesKürzel As String
    Dim NeuesKürzel As String, raFund As Range
    AlteBezeichnung = ComboBox1.Value
    NeuesKürzel = ActiveSheet.Cells(last, 1).Value
    Set raFund = ActiveSheet.Columns(2).Find(AlteBezeichnung, LookIn:=xlValues, LookAt:=xlWhole)

    If Not raFund Is Nothing Then
        AltesKürzel = raFund.Offset(, -1).Value
        With Sheets(ActiveSheet.Index + 1)
            .Range("B:B").Replace What:=AltesKürzel, Replacement:=NeuesKürzel, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        End With
    Else
        MsgBox "Suchbegriff nicht vorhanden."
    End If
End Sub

Tipps für Profis

Verwende Option Explicit: Dies zwingt dich, alle Variablen zu deklarieren, was Fehler vermeidet.
Nutze Set für Objekte: Bei der Zuweisung von Objekten wie Range solltest du immer Set verwenden.
Testen von Code: Teste deinen Code Schritt für Schritt, um schnell Fehler zu finden und zu beheben.

FAQ: Häufige Fragen

1. Wie kann ich eine Zelle in einer Formel mit einer Variable definieren? Du kannst eine Variable in einer Formel definieren, indem du sie in einer Zeichenkette verwendest. Beispiel: Range("A1").Formula = "=SUM(B1:B10) + " & AltesKürzel

2. Was ist der Vorteil der Find-Methode? Die Find-Methode ist effizienter als das Durchlaufen von Zellen, da sie gezielt nach dem Suchbegriff sucht und in großen Datenmengen schneller Ergebnisse liefert.

Beliebteste Forumthreads (12 Monate)

Forumthread: Variable über Find definieren

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Variable über Find definieren in Excel VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Variable über Find definieren

Forumthread: Variable über Find definieren

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Variable über Find definieren in Excel VBA

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread