Spalten format ändern

Bild

Betrifft: Spalten format ändern
von: Thomas
Geschrieben am: 30.07.2015 15:57:03

Hallo Excelfreunde,
ich bastle immer noch an mein kleines Problem. ich habe schon eine fast Lösung gefunden.
Mit Hilfe des untenstehenden Makro such ich in der 4. Zeile die Wörter Test 1 und Menge. Nach dem Fund formatiere ich die Spalte ins Uhrzeit Format.
Ich habe nur noch das Problem das ich es einfach nicht schaffe das dies Makro sich die Zeile mit der Überschrift selbst sucht. denn Sie steht mal in Zeile 5 mal in Zeile 6 usw.
Habe schon mit "ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row" versucht zu spielen aber es will nicht gelingen
Kann mir jemand dabei helfen?
liebe grüsse thomas

Sub SpaltenFormatierenSchleife()
Dim Spalte As Integer
Sheets("Tabelle1").Select
With Sheets("Tabelle1")
For Spalte = 1 To .UsedRange.Columns.Count
If .Cells(4, Spalte).Value = "TEST 1" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
ElseIf .Cells(4, Spalte).Value = "Menge" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
End If
Next Spalte
End With
End Sub

Bild

Betrifft: AW: Spalten format ändern
von: Werner
Geschrieben am: 30.07.2015 16:33:52
Hallo Thomas,
das sollte doch mit einer zweiten Schleife über die Zeilen gehen.

Sub SpaltenFormatierenSchleife()
Dim Spalte As Long
Dim Zeile AS Long
Sheets("Tabelle1").Select
With Sheets("Tabelle1")
For Spalte = 1 To .UsedRange.Columns.Count
For Zeile = 4 To .UsedRange.Rows.Count
If .Cells(Zeile, Spalte).Value = "TEST 1" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
ElseIf .Cells(Zeile, Spalte).Value = "Menge" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
End If
Next Spalte
Next Zeile
End With
End Sub
Habe es aber nicht getestet.
Gruß Werner

Bild

Betrifft: AW: Spalten format ändern
von: Thomas
Geschrieben am: 30.07.2015 19:46:44
Hallo Werner,
erstmal besten Dank für deine Hilfe.
Aber irgentetwas gefällt excel noch nicht so ganz.
in der Zeile "Next Spalte" schreibt es als Fehler " ungültiger Verweis auf next steuervariable".
kannst Du was damit anfangen?
liebe grüsse thomas

Bild

Betrifft: AW: Spalten format
von: Thomas
Geschrieben am: 30.07.2015 19:59:56
Hallo,
habe was gefunden
habe die zwei
Next Spalte
Next Zeile
getauscht jetzt gehts. Läuft nur etwas langsam durch aber es läuft. gibt es noch ein trick um es ein wenig schneller zu machen?
liebe grüsse thomas

Sub SpaltenFormatierenSchleife_neu()
Dim Spalte As Long
Dim Zeile As Long
'Sheets("Tabelle1").Select
With Sheets("Tabelle1")
For Zeile = 1 To .UsedRange.Rows.Count
For Spalte = 1 To .UsedRange.Columns.Count
If .Cells(Zeile, Spalte).Value = "TEST 1" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
ElseIf .Cells(Zeile, Spalte).Value = "Menge" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
End If
Next Spalte
Next Zeile
End With
End Sub


Bild

Betrifft: AW: Spalten format
von: Werner
Geschrieben am: 30.07.2015 20:38:31
Hallo Thomas,
sorry, war mein Fehler, dass ich da die Reihenfolge vertauscht habe. Das mit der Länge der Laufzeit liegt wohl daran, dass jede Zelle im kompletten Bereich abgeklappert wird. Und das auch dann, wenn der Begriff schon in der ersten überprüften Zelle vorhanden ist.
Stellt sich die Frage, ob Test 1 oder Menge mehrfach an unterschiedlichen Stellen vorkommen kann. Kann es auch sein, dass sowohl Test 1 als auch Menge gleichzeitig vorhanden ist oder jeweils nur entweder Test 1 oder Menge. Ich vermute ja, dass du Überschriften suchst, dann könnte man ja nach dem Fund den Code abbrechen.
Versuch mal:

Sub SpaltenFormatierenSchleife_neu()
Dim Spalte As Long
Dim Zeile As Long
'Sheets("Tabelle1").Select
With Sheets("Tabelle1")
For Zeile = 1 To .UsedRange.Rows.Count
For Spalte = 1 To .UsedRange.Columns.Count
If .Cells(Zeile, Spalte).Value = "TEST 1" Or .Cells(Zeile, Spalte).Value = "Menge" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
Exit Sub
End If
Next Zeile
Next Spalte
End With
End Sub
Konnte es leider wieder nicht testen, nur ein Versuch.
Gruß Werner

Bild

Betrifft: AW: Spalten format
von: Thomas
Geschrieben am: 30.07.2015 21:47:05
Hallo Werner,
du brauchst nicht sorry zu sagen ohne solche Menschen wie Du einer bist wäre ich nie mit dem Projekt so weit gekommen. Vielen dank dafür.
Das makro ist jetzt rasend schnell geworden nur leider hört es wahrscheinlich schon beim ersten Spaltenfund auf.
Es wird nur die Spalte Test 1 formatiert.Hab mal versucht das or mit and zu tauschen aber es funktioniert auch nicht. Die Begriffe kommen jeweils nur einmal vor nur in der Überschrift.
Ich habe mal versucht die zeilen zahl auszulagern mit den Plan sie mit einer Formel zu suchen aber dies ging auch deneben. Mein fehlversuch bitte nicht lachen war nur ne idee:
liebe Grüsse thomas

Sub SpaltenFormatierenSchleife()
Dim Spalte As Integer
Dim Zeile As Integer
Sheets("Tabelle1").Select
With Sheets("Tabelle1")
For Spalte = 1 To .UsedRange.Columns.Count
Zeile = Sheets("auswertung").Cells.Find(Range("B5").Value).Columns.Count
If .Cells(Zeile, Spalte).Value = "TEST 1" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
ElseIf .Cells(Zeile, Spalte).Value = "Menge" Then
.Columns(Spalte).NumberFormat = "h:mm;@"
End If
Next Spalte
End With
End Sub


Bild

Betrifft: AW: Spalten format
von: Werner
Geschrieben am: 30.07.2015 22:02:19
Hallo Thomas,
sind die Begriffe Test 1 und Menge immer in der gleichen Zeile?
Werner

Bild

Betrifft: AW: Spalten format
von: Thomas
Geschrieben am: 30.07.2015 22:43:06
Hallo Werner,
nein leider nicht sie sind mal Zeile 3 mal in zeile 4. Das einzige was fest ist Sie kann niemals tiefer als zeile 10 sein. Auch die spalten können über all sein eigendlich bis zz, Hab auch schon überlegt ob man erst in zeile 1 dann in zeile 2 unsw. sucht und wenn eins von den beiden gefunden wurde dann die zeilennummer benutzt aber das bekomme ich auch nicht hin. deshalb war mein gedanke Sie mit soetwas wie
=MAX(WENN(Auswertung!A1:J16=H1;ZEILE(Auswertung!A1:J16)))
zu suchen und Sie dann ins Makro zu übergeben.
liebe grüsse thomas

Bild

Betrifft: AW: Spalten format
von: Werner
Geschrieben am: 31.07.2015 09:42:36
Hallo Thomas,
Versuch mal das.

Sub SpaltenFormatierenSchleife()
      Dim Spalte As Long
      Dim Zeile AS Long
      Dim Aus AS Boolean
      Dim Aus1 AS Boolean
      
      
      With Sheets("Tabelle1")
      
      For Zeile = 4 To 10
      For Spalte = 1 To .UsedRange.Columns.Count
      
      If .Cells(Zeile, Spalte).Value = "TEST 1" Then
      .Columns(Spalte).NumberFormat = "h:mm;@"
      Aus = True
      ElseIf .Cells(Zeile, Spalte).Value = "Menge" Then
      .Columns(Spalte).NumberFormat = "h:mm;@"
      Aus1 = True
      End If
      If Aus = True And Aus1 = True Then
      Aus = False
      Aus1 = False
      Exit Sub
      End If
      
      Next Spalte
      Next Zeile
      End With
      End Sub
Gruß Werner

Bild

Betrifft: AW: Spalten format
von: Werner
Geschrieben am: 31.07.2015 10:19:12
Hallo Thomas,
hab noch einen Fehler drin. Mit dem Code

For Zeile = 4 To 10
sucht er ja nur von Zeile 4 bis Zeile 10. Wenn die Begriffe schon ab Zeile 1 vorkommen können dann
For Zeile = 1 To 10
Sucht von Zeile 1 bis Zeile 10.
Gruß Werner

Bild

Betrifft: perfekt besten dank an werner
von: Thomas
Geschrieben am: 31.07.2015 11:01:24
Hallo Werner,
dies ist perfekt. vielen vielen dank es ist super schnell auch noch bei 3000 zeilen.
Nun kann ich mit mein Project weitermachen.
klasse
liebe grüsse thomas

Bild

Betrifft: AW: Danke für die Rückmeldung. o.w.T.
von: Werner
Geschrieben am: 31.07.2015 11:13:08


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Spalten format ändern"