Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1860to1864
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

Wenn bestimmter Text in Spalte

Wenn bestimmter Text in Spalte
09.12.2021 13:49:52
Maik
Hallo,
ich habe mit VBA nur wenig Erfahrung und arbeite mich gerade erst in die Thematik ein, bräuchte dennoch erst einmal kurzfristig Hilfe.
Ich möchte gern eine Spalte ansprechen und wenn in dieser immer bestimmtes Wort steht soll der Text rechtsbündig formatiert werden.
Wie kann ich dieses realisieren?

31
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 14:33:44
UweD
Hallo
- Verwende den Autofilter
- Filtere die Spalte nach dem Wort
- Markiere die Spalte und formatiere den Text
- Filter entfernen
- Das ganze kannst du auch mit Makrorecorder aufzeichnen und dann den Code ggf. noch anpassen
LG UweD
AW: Wenn bestimmter Text in Spalte
09.12.2021 14:41:16
Maik
Hallo Uwe,
danke für die rasche Antwort. Das mit der Bedingten Formatierung und dem Recorder habe ich schon probiert, leider ohne Erfolg.
Der Grund ist der, das es in der Bedingten Formatierung die Funktion für das Ausrichten des Textes (zb Rechtsbündig) nicht gibt.
Aus diesem Grund möchte ich das gern per Macro machen. Um es einmal genauer zu erläutern:
Wenn in einer Zelle der Spalte XY das Wort "bla" erscheint dann soll diese Zelle rechtsbündig formatiert werden. Da ich leider Anfänger auf diesem Gebiet bin und nicht genau weiß wie ich Zellen anspreche und auswerte.
VG Maik
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 14:51:01
UweD
Ich hatte "AutoFilter" geschrieben und nichts von "Bedingter Formatierung"
LG UweD
AW: Wenn bestimmter Text in Spalte
09.12.2021 14:40:26
Nepumuk
Hallo Maik,
in ein Standardmodul (Menüleiste im VBA-Editor - Einfügen - Modul):

Option Explicit
Public Sub Rechtsbuendig()
Const SEARCH_TEXT As String = "text" 'Anpassen
Const SEARCH_COLUMN As Long = 3      'Anpassen 3=Spalte C
Dim objCell As Range
Dim strFirstAddress As String
With Columns(SEARCH_COLUMN)
Set objCell = .Find(What:=SEARCH_TEXT, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
objCell.HorizontalAlignment = xlLeft
Set objCell = .FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
Set objCell = Nothing
End If
End With
End Sub
Gruß
Nepumuk
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:02:57
Maik
Vielen Dank, Funktioniert supi. Kann ich statt Spalte auch einen Bereich angeben? Im speziellen habe ich eine Pivot Tabelle und der Code soll in Spalte C soweit laufen oder sich wiederholen wie die Tabellenspalte Zeilen hat.
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:07:37
Nepumuk
Hallo Maik,
es werden alle Zeilen der Spalte berücksichtigt.
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:10:42
Maik
Kann ich im gleichen Code unten noch sagen, wenn ich auf "speichern" drücke, soll er das Macro ausführen?
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:17:25
Nepumuk
Hallo Maik,
nein, dazu benötigst du folgendes Makro im Modul "DieseArbeitsmappe":

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call Rechtsbuendig
End Sub
und du musst im Makro "Rechtsbuendig" diese Zeile:

With Columns(SEARCH_COLUMN)
so ändern:

With Worksheets("Tabelle1").Columns(SEARCH_COLUMN)
Den Tabellennamen musst du natürlich anpassen.
Gruß
Nepumuk
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:42:11
Maik
Danke, hab ich so gemacht, allerdings kommt die Fehlermeldung (in "Dieser Arbeitsmappe") - Variable oder Prozedur anstelle eines Moduls erwartet
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:48:56
Nepumuk
Hallo Maik,
hast du das Modul in "Rechtsbuendig" umbenannt? Ändere den Namen.
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:53:19
Maik
Ja hab ich.
AW: Wenn bestimmter Text in Spalte
09.12.2021 15:59:40
Nepumuk
Hallo Maik,
Standardmodule haben den Präfix "bas" für Basis. Daher: "basRechtsbuendig" 😎
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:11:25
Maik
Macht er leider auch nix. Sorry ich glaub ich stell mich etwas ungeschickt an xD
Ich habe ein Modul das basRechtsbuendig heist. Und in "DieserArbeitsmappe" gebe ich dann Call basRechtsbuendig ein?
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:15:05
Nepumuk
Hallo Maik,
nein, du musst die Prozedur mit Call Rechtsbuendig aufrufen.
Gruß
Nepumuk
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:20:45
Maik
So hatte ich es vorher:
"DieseArbeitsmappe"
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1").Value = Now()
Call Rechtsbuendig
End Sub
"Modul" : basRechstbuendig
Option Explicit

Public Sub basRechtsbuendig()
Const SEARCH_TEXT As String = "Nein"         'Anpassen
Const SEARCH_COLUMN As Long = 3              'Anpassen 3=Spalte C
Dim objCell As Range
Dim strFirstAddress As String
With Worksheets("Tabelle3").Columns(SEARCH_COLUMN)
Set objCell = .Find(What:=SEARCH_TEXT, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
objCell.HorizontalAlignment = xlRight
Set objCell = .FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
Set objCell = Nothing
End If
End With
End Sub
So?
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:25:40
Nepumuk
Hallo Maik,
nein, die Prozedur darfst du nicht umbenennen, nur das Modul. Also:

Public Sub Rechtsbuendig()
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:39:51
Maik
Perfekt, jetzt klappt es :) vielen Dank!! Darf ich dich noch etwas Fragen?
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:44:17
Nepumuk
Hallo Maik,
klar.
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 18:25:21
Maik
Es ist aber ein ganz anderes Thema und ich weiß nicht ob man dazu einen Neuen Beitrag anlegen muss.
Ich schildere es dir einfach mal:
Ich erstelle derzeit ein paar Vorlagen, dazu habe ich mir ein paar codes zusammengebastelt (was ich so gefunden habe)
1. Ich möchte gern die Arbeitsblätter Passwort schützen und dabei bestimmte Bereiche (Zellen) sperren als auch freigeben.
2. Soll dann ein Macro laufen was ich über einen Button anspreche. Der Button hat die Funktion, dass aus einer Spalte mit werten (Die vorher aus einem Programm in diese Tabelle kopiert werden) die Einheiten entfern werden sollen. Aktuell macht er das für jedes Arbeitsblatt. Benötigt wird es nur in 2.
Das Macro funktioniert auch soweit super, allerdings kann man das bestimmt besser lösen um auch die Laufzeit (die derzeit bisschen lang ist) zu verkürzen.
Aktuell läuft es von Zeile 4 bist 300, soll aber nach mglk. bis 500 laufen aber halt nicht so lang.
Vielleicht kannst du mir ja dabei helfen.
Hier der Code des Moduls:

Sub ZeichenketteErsetzen()
'Bestimmte Zeichen (Buchstaben oder Zahlen) suchen und diese durch definierte ersetzen _
Dim xSh As Worksheet
Application.ScreenUpdating = False                      'verhindert das umspringen der Blätter beim Ausführen des Macro _
For Each xSh In Worksheets
xSh.Unprotect Password:="Test"
xSh.Select
Call RunCode
xSh.Protect Password:="Test"
Next
Call Meldung_1                                          'ruft Meldung aus Modul "Meldungen ab _
''Application.ScreenUpdating = True _
End Sub

Sub RunCode()
Dim i As Integer
For i = 4 To 300
Cells(i, 3).Value = Replace(Cells(i, 3).Value, "mm", "")
Cells(i, 3).Value = Replace(Cells(i, 3).Value, "oE", "")
Cells(i, 3).Value = Replace(Cells(i, 3).Value, "oe", "")
Cells(i, 3).Value = Replace(Cells(i, 3).Value, "m", "")
Next i
End Sub
VG Maik
Anzeige
AW: Wenn bestimmter Text in Spalte
09.12.2021 18:47:58
Nepumuk
Hallo Maik,
teste mal:

Option Explicit
Public Sub ZeichenketteErsetzen()
'Bestimmte Zeichen (Buchstaben oder Zahlen) suchen und diese durch definierte ersetzen _
Dim xSh As Worksheet
Application.ScreenUpdating = False                      'verhindert das umspringen der Blätter beim Ausführen des Macro _
For Each xSh In Worksheets
xSh.Unprotect Password:="Test"
Call RunCode(xSh)
xSh.Protect Password:="Test"
Next
Call Meldung_1                                          'ruft Meldung aus Modul "Meldungen ab
Application.ScreenUpdating = True
End Sub
Private Sub RunCode(ByRef xSh As Worksheet)
With xSh
With .Range(.Cells(4, 3), .Cells(.Rows.Count, 3).End(xlUp))
Call .Replace(What:="mm", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=True)
Call .Replace(What:="oE", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=True)
Call .Replace(What:="oe", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=True)
Call .Replace(What:="m", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=True)
End With
End With
End Sub
Das Makro berücksichtigt alle Zellen in Spalte C von Zeile 4 bis zur letzten benutzen Zeile.
Gruß
Nepumuk
Anzeige
AW: Wenn bestimmter Text in Spalte
10.12.2021 14:09:38
Maik
Hallo Nepumuk,
danke erst einmal für deine Hilfe! Es funktioniert super und auch ziemlich flott, das reicht mir so völlig zu.
Wir hatten das ja noch mit dem Rechtsbündig, was auch funktionierte. Dies wollte ich gern beim speichern aktivieren, aber das behindert sich dann leider mit dem Blattschutz, wenn der aktiv ist kommt dann der Laufzeitfehler. Daher hatte ich mir gedacht die Funktion gleich mit dem Button, wenn die Einheiten entfern werden mit zu machen.
Wie kann ich das implementieren und wie kann ich dem Rechtsbündig sagen, er solle noch ein 2. Arbeitsblatt mit durchlaufen (Spalte bleibt die Gleiche nur eine andere Tabelle)
VG Maik
Anzeige
AW: Wenn bestimmter Text in Spalte
10.12.2021 14:31:21
Nepumuk
Hallo Maik,
dann ändere hier:

Public Sub ZeichenketteErsetzen()
'Bestimmte Zeichen (Buchstaben oder Zahlen) suchen und diese durch definierte ersetzen _
Dim xSh As Worksheet
Application.ScreenUpdating = False                      'verhindert das umspringen der Blätter beim Ausführen des Macro _
For Each xSh In Worksheets
xSh.Unprotect Password:="Test"
Call RunCode(xSh)
If xSh.Name = "Tabelle1" Or xSh.Name = "Tabelle2" Then Call Rechtsbuendig(xSh) 'Tabellennamen anpassen !!!
xSh.Protect Password:="Test"
Next
Call Meldung_1                                          'ruft Meldung aus Modul "Meldungen ab
Application.ScreenUpdating = True
End Sub
Und hier:

Public Sub Rechtsbuendig(ByRef objWorksheet As Worksheet)
Const SEARCH_TEXT As String = "text" 'Anpassen
Const SEARCH_COLUMN As Long = 3      'Anpassen 3=Spalte C
Dim objCell As Range
Dim strFirstAddress As String
With objWorksheet.Columns(SEARCH_COLUMN)
Set objCell = .Find(What:=SEARCH_TEXT, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirstAddress = objCell.Address
Do
objCell.HorizontalAlignment = xlLeft
Set objCell = .FindNext(After:=objCell)
Loop Until objCell.Address = strFirstAddress
Set objCell = Nothing
End If
End With
End Sub
Gruß
Nepumuk
Anzeige
AW: Wenn bestimmter Text in Spalte
10.12.2021 17:21:38
Maik
Hab es mal probiert, da bringt er einen Fehler beim Kompilieren: Falsche Anzahl an Argumenten oder ungültige Zuweisung zu einer Eigenschaft.
If xSh.Name = "Stueckliste" Or xSh.Name = "Nur Bauteile" Then Call BG_Übersicht(xSh)
Dabei markiert er mir Call BG_Übersicht (habe ich anstelle von Rechtsbuendig genommen) - das Sub heisst auch BG_Übersicht und das Modul basRechsbuendig
AW: Wenn bestimmter Text in Spalte
10.12.2021 17:28:41
Nepumuk
Hallo Maik,
hast du die Einsprungszeile der Prozedur auch so geändert?

Public Sub BG_Übersicht(ByRef objWorksheet As Worksheet)
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
10.12.2021 17:49:54
Maik
Ah ok, Da in Klammer hat gefehlt xD jetzt geht es. Geil :)
Wenn ich die Datei jetzt schließe über x (oben rechts) bekomme ich jetzt auch einen Fehler. Kannst du mir sagen was da passiert?
Er sagt Fehler beim Kompilieren, Variable nicht definiert. Es handelt sich dabei um Zeile 6 Cancel
Dieser Code stand schon da und ist in: "DieserArbeitsmappe"

Sub Force_Extension()
'  MsgBox "Datei-Format: " & Me.FileFormat   ' Testzeile
Dim iFilterIndex As Integer
If Me.Path = "" Then 'Prüfen, ob Datei schon gespeichert wurde
Application.EnableEvents = False
Cancel = True
If Val(Left(Application.Version, 2)) >= 12 Then 'Excel-Versionsprüfung
'Excelversion 2007 und neuer
Select Case Me.FileFormat
Case 52             'xlOpenXMLWorkbookMacroEnabled - Excel 2007 mit Makros
iFilterIndex = 2  'Standardmäßig xlsm  unter Excel 2007
Case 56             'xlExcel8  - Excel 98 bis 2003
iFilterIndex = 4  'Standardmäßig xls  unter Excel 2007
Case Else
iFilterIndex = 1  'Standardmäßig xlsx  unter Excel 2007
End Select
'Speichern unter Dialog anzeigen
With Application.FileDialog(2)  '2 = msoFileDialogSaveAs
.InitialView = 2              '2 = msoFileDialogViewDetails
.FilterIndex = iFilterIndex
If .Show  False Then
.Execute
End If
End With
Else
'Ältere Excelversionnen - SaveAs Dialog anzeigen
Application.Dialogs(5).Show     '5 = xlDialogSaveAs
End If
Application.EnableEvents = True
End If
End Sub

AW: Wenn bestimmter Text in Spalte
10.12.2021 17:53:12
Nepumuk
Hallo Maik,
lösch die Zeile, die hat in dieser Prozedur keine Funktion.
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
10.12.2021 18:03:29
Maik
Perfekt, vielen Dank.
Diese Datei wird dann eine Vorlage (.xltm) die wird dann an verschiedenen Stellen benuzt. Nun soll es aber nicht so sein das diese Benutzer dann in den Entwicklertools herumschauen und spielen sowie das "Passwort" sehen. Kann man das verhinder das andere nutzer die die Datei dann speichern unter .xlsm und der Vorlage in die Entwicklertools kommen oder kann man die schützen?
AW: Wenn bestimmter Text in Spalte
10.12.2021 18:14:53
Nepumuk
Hallo Maik,
im VBA-Editor - Menüleiste - Extras - Eigenschaft von VBAProjekt - Reiter "Schutz" - Häkchen bei "Projekt für Anzeige sperren" setzen und ein Kennwort vergeben.
Der Schutz ist erst nach dem Schließen und dem erneuten Öffnen der Mappe wirksam. Wer sich gut auskennt, kann den Schutz aushebeln, aber für den "Normaluser" ist er ausreichend.
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
10.12.2021 18:26:33
Maik
Das reicht mir so, alles weitere ist dann Vorsatz.
Wenn ich mein Blatt mit dem Blattschutz über VBA generiere, kann ich da noch einfügen das die Funktion der Formatierung freigeschaltet bleibt? (Rahmen und Farben..)
AW: Wenn bestimmter Text in Spalte
10.12.2021 18:31:02
Nepumuk
Hallo Maik,
so:

xSh.Protect Password:="Test", AllowFormattingCells:=True
Gruß
Nepumuk
AW: Wenn bestimmter Text in Spalte
09.12.2021 16:29:06
Daniel
Hi
1. du musst beim aufruf mit Call schon den richtigen Makronamen verwenden.
wenn das Makro "basRechtsbuendig" heißt (also der text nach "Sub"), dann musst du es auch genauso aufrufen, mit "Call basRechtsbuendig".
2. du solltest Modul und Makro nicht den gleichen Namen geben. Das kann man zwar machen, aber dann muss man das Makro mit Call Modulname.Makroname aufrufen, also mit Call basRechtsbuendig.basRechtsbuendig
der Aufruf mit Call Modul.Makro ist eigentlich die korrekte Form.
Call geht davon aus, dass der erste Text das Modul ist. Nur wenn es das Modul nicht finden kann, sucht es den Text in den vorhandenen Makros. Findet es jedoch das Modul, dann fehlt das auszuführende Makro.
Gruß Daniel

319 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige