✔ VBA-Excel - Bezug auf Spalte mit bestimmten Namen

S

silkeR

Mitglied
Hallo,

wie kann ich mich bei VBA nicht auf feste Zellen beziehen (z.B. C1:C8000), sondern wie kann ich angeben, dass ich meine Schleife auf die Spalte beziehen will, die die Spaltenüberschrift "Zweck" hat (= Zellinhalt der ersten Zeile). Problem ist, dass ich einen Vorgang automatisieren will, aber die Spalte "Zweck" ja nun nicht jedes Mal in Spalte C steht, aber sie heißt immer "Zweck". Und nur für diese Spalte will ich die Schleife durchlaufen lassen.

Bsp: will ListBox füllen mit den Zellinhalten wo in der ersten Zelle "Zweck" steht und mich eben nicht auf C beziehen, weil ist ja nicht immer C:
ListBoxFuellen lst_meineliste, ThisWorkbook.Worksheets("meinTab_blatt").Range("C2:C7058") - (in C1 steht "Zweck")
Wie löse ich das nun mit dem Range


Hat jemand ein Tipp für mich. Danke schon im Voraus.
Grüße, Silke
 
Du könntest in einer Schleife alle Zellen der ersten Reihe durchsuchen, wenn du das Wort gefunden hast passt es.

Eine andere Lösung ist die integrierte Suchfunktion, mit
Visual Basic: 
 Range("A1").Select
 Cells.Find(What:="Zweck", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  False, SearchFormat:=False).Activate
 curColumn = ActiveCell.Column
durchsuchst du alle Zeilen ab der ersten solange, bis er das Wort findet. Dann springt er in diese Zelle, du kannst dann die Spaltennummer auslesen (hier in eine Variable).


Der Doc!
 
VBA-Excel - Schleife durchlaufen in bestimmter Spalte

Hallihallo,

besten Dank für den Tipp. Jetzt komme ich zwar in meine Spalte die ich will, aber jetzt steht auch der Wert der ersten Zelle mit in der Listbox (sozusagen die Spaltenüberschrift). Wollte aber erst mit dem zweiten anfangen und auch nur bis dahin, wo was drin steht (ListCount) und nicht wie jetzt bis 65.000 und ä bissl.

Irgendwie kriege ich das mit der aktiven Zelle nicht auf die Reihe, das die weiter springt bzw. ich mich eben durch die Spalte bewegen kann. Deshalb funktioniert auch meine Schleife noch nicht – code ist unten. Die Schleife wird zwar durchlaufen, aber machen tut die nur für den ersten Wert was (eben bei der aktiven Zelle und die bleibt ja – dummerweise im Moment noch die Spaltenüberschrift). Wie durchsuche ich die Spalte nach Werten (nicht mit Cells.Find – das geht bei der Aufgabe nicht). Die Spalte muss nach Werten durchsucht werden, die zuvor in einer Listbox angeklickt werden.

Vielen Dank für Hilfe und Hinweise.

Silke

Code: 
Private Sub cmd_code_Click()

Range("A1").Select
Cells.Find(What:="Zweck", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  False).Activate
curcolumn = ActiveCell.Column
‘springt in die Spalte die mit der Schleife durchsucht werden soll


For i = 0 To lst_meineliste.ListCount - 1        'sind noch die 65.000 Zellen von Excel, weil ich im Moment noch die komplette Spalte in die Liste einlade

    If ActiveCell.Value = lst_meineliste.Value Then
        ActiveCell.Offset(0, 1) = cb_refliste.Value

    End If
'wie komme ich hier zur nächsten Zelle/Zeile in der Spalte "Zweck"?

Next
End Sub
 
Hab hier einen code für dich:
Visual Basic: 
 Range("A1").Select
 Cells.Find(What:="Zweck", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
  :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
  False, SearchFormat:=False).Activate
 curColumn = ActiveCell.Column
 
 maxZeile = 50 'Letze Zeile der Spalte angeben
 'Wenn deine Spalten alle so ziemlich gleich lang sind und keine großen Unterschiede bestehen
 '(deine Spalte nicht um >500 Zeilen kürzer als die längste) kannst du auch folgendes nehmen:
 'Set lastcell = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Rows
 'maxzeile = lastcell.Row
 For i = 0 To lst_meineliste.ListCount - 1 'Alle Einträge der Liste
  Range(Cells(curColumn, 1), Cells(curColumn, maxZeile)).Select 'Alle Inhalte der Spalte wählen
  Set xyz = Cells.Find(What:=lst_meineliste.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
   :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
   False, SearchFormat:=False).Activate 'und nur darin suchen
  If xyz Then 'wenn gefunden
   Gefunden = ActiveCell.Row 'Welche Zeile
   'Hier noch dein Code, was du machen willst
  End If
 Next cnt

Erster Teil wie gehabt, er sucht deine Überschrift. darunter ist dann eine Lösung über die Excel-Suche (ist die schnellste Lösung). Dazu musst du irgendwie die letzte Zeile angeben (siehe Kommentar im Code, gibt auch eine Funktion dazu), dann wird die Spalte ausgewählt und in einer Schleife werden alle Werte aus deiner Liste durch die Excel-Suche gejagt. Wenn ein Element gefunden wurde kannst du dann direkt darauf reagieren, ansonsten läuft er die Schleife durch.

Komischerweise bricht er mit einer Fehlermeldung ab (Fehler 91), wenn er nichts findet, müsstest du halt mit einer Fehlerbehandlung abfangen. Probiers mal aus, sonst frag wieder.


Der Doc!
 
Hi,

besten Dank für deine Hilfe. Mmmh, leider geht's immer noch ni. Es hat sich eigentlich nichts funktionelles geändert. Jetzt wird zwar der Bereich selektiert den die Schleife durchlaufen soll, aber gemacht wird weiterhin nur was für die aktive Zelle und die bleibt starr und steif auf der zweiten Zeile stehen.
Bei Set xyz kommt bei mir ne Fehlermeldung Typen unverträglich ?
Die andere Fehlermeldung, das etwas nicht gefunden werden kann, tritt eigentlich durch die Liste nicht auf. Weil in der Listbox kann nur gewählt werden, was tatsächlich in der Spalte steht.

Hauptproblem bleibt also, wie komme ich in der Schleife zur nächsten Zeile, um zu gucken, was da drin steht

Vielleicht hilft es weiter, wenn ich mal die Aufgabe beschreibe: Es geht darum, in einer Listbox sollen alle Werte (gefiltert, also alle nur einmal) ausgelesen werden die in der Spalte „Zweck“ stehen (von Zeile 2 bis Anzahl der Spalteneinträge - variiert). Wenn in Listbox ein Wert (= Wort) angeklickt wird, soll für jede Zelle der Spalte „Zweck“ die dieses Wort enthält in eine andere Spalte der Wert eingetragen werden, der in einer Combobox ausgewählt wurde.
Vielleicht gibt es auch einen ganz anderen Ansatz den man verfolgen kann. Im Prinzip muss ich also die Werte die in Listbox angeklickt wurden, mit Wert in Spalte vergleichen und wenn das übereinstimmt, soll der Wert aus der Combobox, der da angeklickt wurde, in eine neue Spalte geschrieben werden. Wenn nicht, dann soll in der nächsten Zelle geguckt werden, ob das so ist. Bis zum letzten Wert eben.

Vielen Dank schon im Voraus.

Silke
 
Tu mir einen Gefallen und lad deine Excel-Datei als Anhang hier hoch (gezippt). Ich kann mir das ganze dann ansehen und besser beurteilen.


Der Doc!
 
Hallo,

ich habe ein Problem, das mit den hier veröffentlichten Code-Stücken zusammenhängt. Daher hänge ich meine Frage einfach mal hier an.

Ich durchlaufe in einer Schleife alle benutzten Reihen und lese dabei Daten aus zwei bestimmten Spalten aus. Diese habe ich momentan sozusagen fest drin ("B" & "S"), möchte sie aber nun dynamisch halten, damit sich das Format der Tabelle ändern kann ohne dass ich meinen Code anpassen muss. Daher suche ich nach "fullname" und "Veranstaltungen" in der ersten Zeile und möchte dann in der Schleife auf die Zellen zugreifen. Ich habe den hier geposteten Code verwendet und dann für mein Programm adaptiert. Nun bekomme ich jedoch immer einen Laufzeitfehler auf, wenn ich versuche auf die Ranges zuzugreifen. Die Spalten bestimmt er noch korrekt, d.h. er greift mit nameColumn=2 und eventColumn=19 zu.

Kann mir da einer weiterhelfen? Unten findet ihr meinen bisherigen Code.
Danke!!

Code: 
   Dim xlSheet As Excel.Worksheet
   Dim strName As String
   Dim strCell As String
   Dim strCellID As String
   Dim count As Integer
   Dim i As Integer
   Dim maxRows, nameColumn, eventColumn As Integer
        
   Set xlSheet = Sheets(1)
   count = 2
  
   maxRows = xlSheet.UsedRange.Rows.count
      
   xlSheet.Range("A1").Select
   xlSheet.Cells.Find(What:="fullname", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
   
   nameColumn = ActiveCell.column
   
   xlSheet.Range("A1").Select
   xlSheet.Cells.Find(What:="Veranstaltungen", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
   
   eventColumn = ActiveCell.column
   
   For i = 2 To maxRows
    
    'Funktioniert
    'strCellID = "B" & i
    'strName = xlSheet.Range(strCellID).Value
    'strCellID = "S" & i
    'strCell = xlSheet.Range(strCellID).Value

    strName = xlSheet.Range(Cells(nameColumn, i)).Value
    strCell = xlSheet.Range(Cells(eventColumn, i)).Value
    
    Call DatenAufbereiten(i, strName, strCell, count)
   Next i
 
Zuletzt bearbeitet:
Ok, ich habs hinbekommen, lag an meinem Range

Code: 
 Dim xlSheet As Excel.Worksheet
   Dim strName As String
   Dim strCell As String
   Dim strCellID As String
   Dim count As Integer, obRng As Object
   Dim i As Integer, searchArr()
   Dim maxRows As Long, nameColumn As Integer, eventColumn As Integer
   Set xlSheet = Sheets(1)
   count = 2
  searchArr = Array("fullname", "Veranstaltungen")
  With xlSheet
       maxRows = .Cells.SpecialCells(xlCellTypeLastCell).Row
        For i = 0 To 1
            Set obRng = .Rows(1).Find(What:=searchArr(i), LookIn:=xlFormulas, LookAt _
             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
             If Not obRng Is Nothing Then
                     If i = 0 Then
                        nameColumn = obRng.Column
                     Else
                        eventColumn = obRng.Column
                     End If
                     Set obRng = Nothing
                 Else: MsgBox "nix gefunden ": Exit Sub
             End If
       Next
       For i = 2 To maxRows
        'Funktioniert
        'strCellID = "B" & i
        'strName = .Range(strCellID).Value
        'strCellID = "S" & i
        'strCell = .Range(strCellID).Value
    
        'Laufzeitfehler
        strName = .Cells(i, nameColumn).Value
        strCell = .Cells(i, eventColumn).Value
       Call DatenAufbereiten(i, strName, strCell, count)
     Next i
End With
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück