Variablenwert suchen und Spalte ausgeben

Forumthread: Variablenwert suchen und Spalte ausgeben

04.04.2017 11:57:14

Kian

Hallo zusammen,
schreibe grade ein Makro, das in einer gewissen Range einen Wert sucht und durch einen anderen ersetzt. Soweit kein Problem. Es wird allerdings etwas tricky, da der zu suchende Wert und der ersetzende Wert sich regelmäßig ändern.
- Je nach Monat muss er den Spaltenbuchstaben anpassen und durch den neuen ersetzen
Makro liegt in "Tabelle1". Es soll also in "Tabelle3" nach dem Wert von der Variable "S1" suchen (in der "Tabelle3" stehen die Werte, die er durchsuchen soll in Zeile 2). Wenn er das Match gefunden hat, soll er mir den Spaltenbuchstaben der Spalte als Variable übergeben. An dieser Stelle hakt es dann. Kriege die Find bzw. die Umwandlung in den Spaltenbuchstaben nicht hin -.- Jemand eine kluge Idee?
nochmal kurz zur Veranschaulichung.
z.B: S1 = "Feb-17"
S2 = "Mrz-17"
Er soll mir jetzt in Tabelle3 in Zeile 2 "Feb-17" raussuchen und die Spaltenummer ausgeben (bspw. "AX")
Dazu die Spaltennummer vom Match von "Mrz-17" ("AY", steht eine Spalte rechts neben "Feb-17")
"AX" muss er jetzt in Tabelle1.Range("E45:AM59") suchen und dann durch "AY" ersetzen.
Und da hakt es grade gewaltig bei mir -.-
Vielen Dank und Beste Grüße,
Kian

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variablenwert suchen und Spalte ausgeben

04.04.2017 12:21:16

Rudi

Hallo,
wozu willst du den Spaltenbuchstaben? Die Nummer reicht doch.
Spalte = Application.Match(S1,Tabelle3.Rows(2),0)
Buchstabe=replace(cells(1,Spalte).Address(0,0),"1","")
Gruß
Rudi

AW: Variablenwert suchen und Spalte ausgeben

04.04.2017 15:00:00

Kian

Hallo Rudi,
vielen Dank für deinen Einsatz. Ich bin jetzt doch selber drauf gekommen :)
Funktioniert alles.
Besten Dank.

AW: Variablenwert suchen und Spalte ausgeben

04.04.2017 15:20:21

Werner

Hallo Kian,
schön wäre jetzt noch, wenn du andere auch an deinem Erfolg teilhaben lassen würdest, indem du deinen Code postest.
Davon lebt ein Forum schließlich.
Gruß Werner

AW: Variablenwert suchen und Spalte ausgeben

04.04.2017 15:49:00

Kian

Hallo Werner,
da hast du natürlich recht :) Hat mich wohl die spontane Freude es geschafft zu haben, überwältigt. ;)
Anbei der Code:


Sub BezugAkt
Dim CAY1,CAY2, SA1,SA2 as String
Dim SpalteA1,SpalteA2 as Long
'Suchvariable: Alter Monat
SA1 = DateSerial(Year(Date), Month(Date) - 2, 1)
'Suchvariable: Monat des aktuellen Monitorings
SA2 = DateSerial(Year(Date), Month(Date) - 1, 1)
'Spaltennummer des alten Monats heraussuchen (Werte in Zeile 2)
SpalteSA1 = Worksheets("Tabelle3").Rows(2).Find(What:=SA1, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
'Spaltenummer des aktuellen Monats heraussuchen (Werte in Zeile 2)
SpalteSA2 = Worksheets("Tabelle3").Rows(2).Find(What:=SA2, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
'Umwandeln der Spaltenummern in Spaltenbuchstaben
CAY1 = Split(ActiveSheet.Cells(2, SpalteSA1).Address, "$")(1)
CAY2 = Split(ActiveSheet.Cells(2, SpalteSA2).Address, "$")(1)
'Ersetzen der Zellbezüge in den Formeln: Aktueller Monat statt alter Monat.
ActiveSheet.Range("E45:AM59").Replace What:=CAY1, _
Replacement:=CAY2, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=True, _
SearchFormat:=False, _
ReplaceFormat:=False

Der Bericht wird immer im Folgemonat erstellt, daher ist der "aktuelle" Monat der Vormonat.
BG
Kian

Infobox / Tutorial

Variablenwert in Excel suchen und Spalte ausgeben

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

Um in Excel einen Wert in einer Zeile zu suchen und die entsprechende Spalte auszugeben, kannst Du VBA verwenden. Hier ist eine Schritt-für-Schritt-Anleitung, die Dir hilft, dies zu erreichen:

Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu, indem Du mit der rechten Maustaste auf ein Projekt klickst und „Einfügen“ > „Modul“ wählst.

Kopiere den folgenden VBA-Code in das Modul:

Sub BezugAkt()
   Dim CAY1, CAY2, SA1, SA2 As String
   Dim SpalteSA1, SpalteSA2 As Long

   ' Suchvariable: Alter Monat
   SA1 = DateSerial(Year(Date), Month(Date) - 2, 1)
   ' Suchvariable: Monat des aktuellen Monitorings
   SA2 = DateSerial(Year(Date), Month(Date) - 1, 1)

   ' Spaltennummer des alten Monats heraussuchen (Werte in Zeile 2)
   SpalteSA1 = Worksheets("Tabelle3").Rows(2).Find(What:=SA1, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column

   ' Spaltenummer des aktuellen Monats heraussuchen (Werte in Zeile 2)
   SpalteSA2 = Worksheets("Tabelle3").Rows(2).Find(What:=SA2, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column

   ' Umwandeln der Spaltennummern in Spaltenbuchstaben
   CAY1 = Split(ActiveSheet.Cells(2, SpalteSA1).Address, "$")(1)
   CAY2 = Split(ActiveSheet.Cells(2, SpalteSA2).Address, "$")(1)

   ' Ersetzen der Zellbezüge in den Formeln: Aktueller Monat statt alter Monat.
   ActiveSheet.Range("E45:AM59").Replace What:=CAY1, Replacement:=CAY2, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End Sub

Schließe den VBA-Editor und kehre zu Excel zurück.
Führe das Makro aus, indem Du ALT + F8 drückst, das Makro auswählst und auf „Ausführen“ klickst.

Mit diesem Code kannst Du den Wert in der Zeile suchen und die Spalte ausgeben, in der der Wert steht.

Häufige Fehler und Lösungen

Fehler: „Objekt nicht gefunden“
- Lösung: Überprüfe, ob der gesuchte Wert tatsächlich in der angegebenen Zeile vorhanden ist. Stelle sicher, dass die Zeile korrekt referenziert wurde.
Fehler: Falsche Spaltennummer
- Lösung: Achte darauf, dass die Suchvariablen (SA1, SA2) korrekt gesetzt sind und die Daten in der richtigen Zeile vorhanden sind.
Das Makro führt keine Ersetzung durch
- Lösung: Stelle sicher, dass der Bereich „E45:AM59“ korrekt definiert ist und dass die Werte in CAY1 und CAY2 tatsächlich existieren.

Alternative Methoden

Eine alternative Methode, um in Excel einen Wert zu suchen und die Spalte auszugeben, ist die Verwendung von Excel-Formeln:

VERGLEICH-Funktion: Mit dieser Funktion kannst Du die Spaltennummer ermitteln, in der ein bestimmter Wert steht.
```
=VERGLEICH("Feb-17", Tabelle3!2:2, 0)
```
INDEX-Funktion: Du kannst die Spaltennummer nutzen, um den entsprechenden Spaltenbuchstaben zu ermitteln.
```
=ADRESSE(1,VERGLEICH("Feb-17", Tabelle3!2:2, 0), 4)
```

Diese Funktionen ermöglichen Dir, die Suche direkt in der Excel-Oberfläche durchzuführen, ohne VBA verwenden zu müssen.

Praktische Beispiele

Beispiel für die Verwendung des VBA-Codes:
- Setze in „Tabelle3“ die Werte „Feb-17“ und „Mrz-17“ in Zeile 2 ein und führe das Makro aus. Du solltest sehen, dass die Werte in dem angegebenen Bereich ersetzt werden.
Beispiel für die Verwendung von Excel-Formeln:
- Nutze die Funktion =VERGLEICH("Feb-17", Tabelle3!2:2, 0) in einer Zelle, um die Spaltennummer von „Feb-17“ zu finden.

Tipps für Profis

Verwende den Option Explicit Befehl am Anfang Deines Moduls, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
Teste Deinen Code regelmäßig, indem Du die Werte in „Tabelle3“ veränderst, um sicherzustellen, dass das Makro flexibel genug ist.
Dokumentiere Deinen Code mit Kommentaren, damit Du oder andere ihn später besser verstehen können.

FAQ: Häufige Fragen

1. Wie kann ich den Spaltenbuchstaben in einer Zelle anzeigen lassen? Um den Spaltenbuchstaben anzuzeigen, kannst Du die Funktion =ADRESSE(1,VERGLEICH("Feb-17", Tabelle3!2:2, 0), 4) verwenden.

2. Kann ich das Makro anpassen, um mehrere Werte gleichzeitig zu ersetzen? Ja, Du kannst eine Schleife verwenden, um mehrere Such- und Ersetzungswerte in einem Durchgang zu verarbeiten. Achte darauf, die Logik entsprechend anzupassen.

Beliebteste Forumthreads (12 Monate)

Variablenwert suchen und Spalte ausgeben

Forumthread: Variablenwert suchen und Spalte ausgeben

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Variablenwert in Excel suchen und Spalte ausgeben

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)

Variablenwert suchen und Spalte ausgeben

Forumthread: Variablenwert suchen und Spalte ausgeben

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Variablenwert in Excel suchen und Spalte ausgeben

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)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread