eine If anwendung auf ganze spalte?

Hallo, Ich möchte gerne mit einer If... and ... Abfrage eine ganze spalte bis zur Zeile 120 durchführen. Muss ich da jetzt für jede Zeile einmal den ganzen Text schreiben wie im Bild gezeigt oder geht das einfacher?

Es wäre auch super wenn die namen mit den ergebnissen in den Reihen "I" und "J" direkt untereinander stehen würden und ggf schon sortiert nach den Ergebnissen in der Reihe "J" mit dem besten ganz oben, falls das geht.

vielen dank schonmal

VBA Versuch - (Microsoft Excel, VBA, If)

1 Antwort

Ninombre

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

17.02.2017, 14:32

was etwas praktischer als ein Screenshot wäre, wäre der Code direkt als Text, dann könnte man den auch ändern - ich tippe das Bild nicht ab.

Man braucht natürlich nicht die Abfragen 120 Mal zu wiederholen, sondern setzt eine Variable ein, die man in einer Schleife durchgeht. In Deinem Fall jeweils die Zeilenangabe durch die Variable ersetzen:

for i=2 to 120
if sheets...(i,2) = "SK" and sheets...(i,3)="LG" then...
end if
next

das mit dem Sortieren habe ich nicht wirklich verstanden, was genau passieren soll.

RigaDone

Beitragsersteller

17.02.2017, 14:43

mhh okee stimmt eigentlich :) dass mit dem reinkopieren macht natürlich mehr sinn :)

wenn die Zellen kopiert sind steht in der Reihe "I" nun ein Name und in der Reihe "J" ein Zahlenwert. Es wäre nun praktisch wenn die Spalten "I" und "J" nach dem Zahlenwert hin sortiert werden. Hier wäre es gut wenn der höchste wert mit dem dazu gehörigen Namen oben steht und abfallend weiter geht

Sub Zelle_kopieren()


   If Sheets("Heim").Cells(2, 2) = "SK" And _

      Sheets("Heim").Cells(2, 3) = "LG" Then


         Sheets("Heim").Cells(2, 1).Copy _

            Destination:=Sheets("Heim").Cells(2, 9)

    End If


    If Sheets("Heim").Cells(3, 2) = "SK" And _

      Sheets("Heim").Cells(3, 3) = "LG" Then


         Sheets("Heim").Cells(3, 1).Copy _

            Destination:=Sheets("Heim").Cells(3, 9)


   End If


End Sub

RigaDone

Beitragsersteller

17.02.2017, 14:49

@RigaDone

oh okee jetzt weis ich was du meintest mit dem sortieren. den zahlenwert habe ich mit sverweis gesetzt. kann man das evtl auch machen dass die zellen "B2" und "D2" kopiert werden? dann könnte ich mir das sverweis sparen

Ninombre

17.02.2017, 15:22

@RigaDone

Du kannst natürlich auch weitere Zellen kopieren, so wie bislang der (name?) aus Spalte A nach I kopiert wird, wenn die beiden Bedingungen erfüllt sind.
Ist es richtig, dass auch die Inhalte von B und D je Zeile kopiert werden sollen?

Da nicht jede Zeile kopiert wird, würde ich abfragen, welche die letzte benutzte Zeile in Spalte I ist, damit keine Lücken entstehen. Wenn das mit dem Kopieren passt, kann man noch das Sortieren einbauen

Sub Zelle_kopieren()
For i = 1 To 120
   If Sheets("Heim").Cells(i, 2) = "SK" And Sheets("Heim").Cells(i, 3) = "LG" Then
    letztezeile = Cells(Rows.Count, 9).End(xlUp).Row 'letzte Zeile in Spalte 9 = I wird ermittelt
    Sheets("Heim").Cells(i, 1).Copy Destination:=Sheets("Heim").Cells(letztezeile + 1, 9) 'Spalte A nach Spalte I kopieren
    Sheets("Heim").Cells(i, 2).Copy Destination:=Sheets("Heim").Cells(letztezeile + 1, 10) ' Spalte B nach Spalte J
    Sheets("Heim").Cells(i, 4).Copy Destination:=Sheets("Heim").Cells(letztezeile + 1, 11) ' Spalte D nach Spalte K
    End If
Next
End Sub

Ähnliche Beiträge

Makro zum Zusammenfassen von Spalten

Hallo alle zusammen,

ich hab ein kleines Problem mit einem Makro, welches ich mir mit Hilfe diverser Quellen versucht habe auf meinen Fall zusammenzuschustern. Folgendermaßen kann man sich die Grundsituation vorstellen:

Ich habe in den Spalten D Und E ca 70 Zeilen mit Einträgen. Bis auf die Zellen 11 - 14 Ist die Spalte E leer. Also wollte ich ein Makro benutzen, welches die Spalte D wenn D und/oder E leer sind mit E zusammenfasst und sonst nicht.

Das soll dann auch für die Spalten F,G und H,I und immer soweiter klappen. Wer könnte mir Helfen und zeigen wo mein Fehler liegt denn bisher funktioniert das Makro nicht ganz so wie ich mir das vorgestellt habe.

Sub Formatierung()

Dim Zeile As Integer Dim Spalte As Integer

For Spalte = 4 To 256 Step 2
For Zeile = 1 To 100

If ((Not (IsEmpty(Cells(Spalte, Zeile)))) = True And IsEmpty(Cells(Spalte + 1, Zeile)) = True) Then
Range(Cells(Zeile, Spalte), Cells(Zeile, Spalte + 1)).Merge
End If

If IsEmpty(Cells(Spalte, Zeile)) = True And IsEmpty(Cells(Spalte + 1, Zeile)) = True Then
Range(Cells(Zeile, Spalte), Cells(Zeile, Spalte + 1)).Merge
End If

Next Zeile

Next Spalte

End Sub

So sieht das bisher aus. Für jede Hilfe vorab schonmal besten Dank

...zum Beitrag

VBA Laufzeitfehler/ Error/ Programm hängt sich auf?

Hallo zusammen,

Ich habe ein Makro geschrieben, mit welchem jeweils via Button eine neue Spalte eingefügt wird.

Über den Button Spalten entfernen wird die jeweils ausgewählte Spalte gelöscht.

Private Sub CmdSpaltePlus_Click() 'Test Spalte hinzufügen

Dim spalte, zeile As Integer

Application.ScreenUpdating = False

spalte = 4

Do Until Cells(2, spalte).Value = "Perfektion"

   spalte = spalte + 1

Loop

Columns("D:E").Copy

Columns(spalte).Insert Shift:=xlToRight

Range(Cells(2, spalte - 2), Cells(2, spalte + 1)).Merge

Range(Cells(4, spalte), Cells(4, spalte + 1)).Merge

Cells(3, spalte).Value = ""

Cells(3, spalte + 1).Value = ""

Cells(4, spalte).Value = ""

Cells(4, spalte + 1).Value = ""

zeile = 6

 

Do Until zeile = 1000

   If Left(Cells(zeile, 1).Value, 1) = "S" Then

        Cells(zeile, spalte).Value = ""

       Cells(zeile, spalte + 1).Value = ""

    End If

   zeile = zeile + 1

Loop

Application.ScreenUpdating = True

End Sub

 

Private Sub CmdPerfektionPlus_Click() 'Perfektion Spalte hinzufügen

Dim spalte, zeile As Integer

Application.ScreenUpdating = False

spalte = 4

Do Until Cells(2, spalte).Value = "F.n.B."

   spalte = spalte + 1

Loop

Columns("D:E").Copy

Columns(spalte).Insert Shift:=xlToRight

Range(Cells(2, spalte - 2), Cells(2, spalte + 1)).Merge

Range(Cells(4, spalte), Cells(4, spalte + 1)).Merge

Cells(3, spalte).Value = ""

Cells(3, spalte + 1).Value = ""

Cells(4, spalte).Value = ""

Cells(4, spalte + 1).Value = ""

zeile = 6

Do Until zeile = 1000

   If Left(Cells(zeile, 1).Value, 1) = "S" Then

        Cells(zeile, spalte).Value = ""

       Cells(zeile, spalte + 1).Value = ""

    End If

   zeile = zeile + 1

Loop

Application.ScreenUpdating = True

End Sub

Spalten löschen

Private Sub Kompetenzenentfernen_Click() 'Spalten löschen

ActiveSheet.Unprotect Password:="MNPS"

If Cells(5, ActiveCell.Column) = "Schicht" Or Cells(5, ActiveCell.Column) = "Nr." Or Cells(5, ActiveCell.Column) = "Name" Or Cells(3, ActiveCell.Column) = "A1" Or Cells(3, ActiveCell.Column) = "D1" Or Cells(9, ActiveCell.Column) = "." Or Cells(3, ActiveCell.Column) = "E1" Or Cells(3, ActiveCell.Column) = "F1" Or Cells(3, ActiveCell.Column) = "G1" Or Cells(3, ActiveCell.Column) = "H1" Or Cells(2, ActiveCell.Column) = "." Or Cells(1, ActiveCell.Column) = "Zielwert" Then

MsgBox "Spalte kann nicht gelöscht werden"

Else

Selection.EntireColumn.Delete Shift:=xlUp

End If

'ActiveSheet.Protect Password:="MNPS"

End Sub

Die Buttons funktionieren auch alle, nur leider kommt oft eine Laufzeitfehlerneldung oder das ganze Programm hängt sich auf wenn ich erst mit dem einen Button eine Spalte eingefügt habe und im Anschluss mit dem anderen Button eine Spalte einfügen.

Es funktioniert also leider nicht verlässlich. Hat jemand eine Idee woran das liegen könnte?

Vielen Dank im Voraus!

...zum Beitrag

Exel subtrahieren?

Hallo

Habe 2 Spalten mit Werten z.B. in Zeile 1 Spalte 3 eine Zahl(234567) in Spalte 4 eine Zahl mit (345678)

in Zeile 2 Spalte 3 456789 in Spalte 4 567890

in Zeile 3 Spalte 3 ist leer in Spalte 4 678901

in Zeile 4 Spalte 3 789012 in Spalte 4 890123

in Zeile 5 Spalte 3 901234 ist leer u.s.w.

Habe sie in einer anderen Spalte mit der Formel =INDEX(K:L;(ZEILE()-16)/2+16;REST(ZEILE()-16;2)+1) sortiert

sodas die Zahlen in der Reihenfolge untereinander angezeigt werden und ich sie

subtrahieren kann. Leider werden die

angezeigten Nullwerte mit gerechnet was zu einem falschen Ergebnis führt. Danke im Voraus.

...zum Beitrag

Werte aus Spalte untereinander auslesen?

Hallo,

ich möchte aus dem Bereich A2:A10 alle Werte auslesen und diese dann jeweils immer in A12 kopieren lassen (ja der Wert soll dann immer wieder überschrieben werden!!!).

Ich dachte dabei an eine For-Schleife, weil ich zuvor auslesen lasse, wie viele Werte es in Spalte A wirklich gibt und dann das entsprechend soft durchlaufen lasse. Gibt es eine Funktion, wo ich die Spalte dann festlege und bspw. mit i=2 für die Reihe in jedem Durchlauf dann die Zeile darunter nehme?

LG Syrena

...zum Beitrag

Excel VBA power query Hilfe?

Hey brauch mal eure Hilfe in vba power query

Ich brauche eine Abfrage if then style

sie muss können :
Montag - Donnerstag = Table.SelectRows(#"Umbenannte Spalten", each Date.IsInNextNDays([DATUM], 1))

jedoch Freitag == Table.SelectRows(#"Umbenannte Spalten", each Date.IsInNextNDays([DATUM], 3))

wie bekommt man diese If Then sachen in power query hin ?
Danke für eure Hilfe

...zum Beitrag

Excel VBA: Bild einfügen, in Variable speichern, Größe ändern, ausrichten?

Ich habe jetzt unzähle Varianten durch. Ich baue ein Bestellsystem für zwei verschieden Shops. Heißt, bei jedem eingetragenen Artikel in die Bestellliste soll am Ende der Listen-Zeile das jeweilige Logo eingefügt werden, kleiner als das Originalbild, kleiner als die Zeile selbst, für die Übersichtlichkeit in der Liste, mittig ausgerichtet.

Ich habe von Pictures.Insert zu Shapes.AddPicture gewechselt, da ich gelesen habe, dass dies die korrektere Form sei. Jetzt bekomme ich eine Fehlermeldung bei der Festlegung eines Rahmens.
Kann mir jemand meine Fehler aufzeigen? Folgende Fragen stellen sich mir:
- Welchen Variablen-Typ brauche ich für ein eingefügtes Bild?
- Wie kann ich diese Variable dann verwenden, um Größe, Position und Rahmen festzulegen?

Danke sehr!!!!!!

Sub LogoEinfügen(Shop As String, Zeile As Integer, Spalte As Integer)

Dim Breite As Integer
Dim Höhe As Integer
Dim strDatei As String
Dim Logo As Object
Dim ShopFarbe As Long, Rot As Long, Blau As Long

' Farben für die zwei Shops festlegen
Rot = 26316
Blau = 13395456

' Dateinamen je nach Shop auswählen und Shop-Farbe einstellen
If Shop = "ZL" Then
  strDatei = "D:\logo1.jpg"
  ShopFarbe = Rot
End If

If Shop = "AQ" Then
  strDatei = "D:\logo2.jpg"
  ShopFarbe = Blau
End If

' Bild einfügen

' Ort festlegen
Dim rg As Range
Set rg = ActiveSheet.Cells(Zeile, Spalte)

' Und einfügen
Set Logo = ActiveSheet.Shapes.AddPicture(strDatei, msoTrue, msoTrue, rg.Left, rg.Top, -1, -1)
Set rg = Nothing

With Logo
      .LockAspectRatio = msoFalse              ' Verzerrung egal
      .Height = Rows(Zeile).RowHeight - 4      ' kleiner als Zeile
      .Width = Columns(Spalte).Width - 4       ' schmaler als Spalte
      .Top = Cells(Zeile, Spalte).Top + (Cells(Zeile, Spalte).Height - Logo.Height) / 2         ' mittig
      .Left = Cells(Zeile, Spalte).Left + (Cells(Zeile, Spalte).Width - Logo.Width) / 2          ' mittig
  End With
  
  ' Rahmen ums Bild, in der Farbe des Shops
  With Logo.ShapeRange.Line
    .Visible = msoTrue
    .ForeColor.RGB = ShopFarbe
    .Weight = 1.5
    .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
End With

Set Logo = Nothing

' Zeilenhöhe anpassen
' Rows(Zeile).RowHeight = Logo.Height
 
End Sub

...zum Beitrag

Excel Zeilen in anderes Blatt kopieren wenn das Datum in der Tabelle kleiner als in die Variabel?

Hallo,

Folgendes Problem: In der Tabelle "Werkzeuge" gibt es in der Spalte D die Spalte "Datum". Das erste Datum ist in der Zelle D5. Anschliessend D6 und so weiter, wie eine normale Tabelle einfach. Nun hab ich auch noch eine Variabel "Date". Jetzt soll verglichen werden, ob die Variabel kleiner als das Datum in der Tabelle ist. Trifft das zu, wird diese Zeile kopiert und im Blatt "Pruefungen" in der Tabelle "Ausgabe" eingefügt. Das soll mit jeder Zeile der Tabelle passieren.

Ich dachte an eine for-Schleife und dann eine if-Abfrage ob die Variabel kleiner ist. Anschliessend wird diese Zeile ,wenn es zutrifft, kopiert. Dann wird auf das andere Blatt gewechselt und dort eingefügt.

Da ich ein Anfänger bin im Thema VBA, würde ich mich freuen, könnte mir jemand behilflich sein. Vielen Dank im voraus.

Gruss flurio

...zum Beitrag

Excel if Abfrage mit aktueller Zeile?

Hallo, in diesem Beispiel möchte ich das, wenn in der Spalte B der Wert 5 ist, er 1 ausgibt und wenn nicht, soll er 0 ausgeben. Ich muss das irgendwie mit =Zeile() lösen, da es später automatisiert werden soll und er sonst nicht die Zahl der aktuellen Zeile weiß. Habt ihr eine Idee, wie ich das am besten machen kann?

...zum Beitrag

Excel Export VBA?

Hey,

ich habe momentan eine Tabelle welche im seitlichen Vormat ist, also jeder Wert steht nebeneinander in Spalten. Also Kategorie Name, Datum, Persönliche Nummer, Aufgabe…

Bei Export möchte ich gerne jede Zeile als Block untereinander haben sodass jeder Person einen Block hat und zwischen jedem Block eine Spalte frei.

Kann mir hier jemand weiterhelfen.

Vielen Danke

...zum Beitrag

Hilfe bei Excel Tabellen Sortierung?

Ich bräuchte Hilfe bei der Tabellen Sortierung in Excel. Die Aufgabe ist folgende: "Hierzu werden alle Arten-Zeilen aus Tabelle 1 (Rohdaten) markiert und durch den Befehl Daten > sortieren nach den Spalten C-L sortiert, so dass alle Arten, die in den ersten zehn Spalten gefunden wurden, untereinander stehen. Deren Zahl ist zu ermitteln.

was genau muss ich machen und anklicken

...zum Beitrag

VBA: Zwei Spalten mit Wert multiplizieren und dann addieren mit Hilfe von Makros

Hallöchen,

ich habe folgendes Problem Ich habe zwei Spalten die ich jeweils miteinander addieren möchte. Doch bevor ich diese addiere, möchte ich Spalte B mit dem Wert aus der TextBox1 multiplizieren und Spalte C mit dem Wert aus Textbox2. Danach sollen beide Spalten miteinander addiert und dann sortiert werden. Spalte D ist in diesem Fall die Ergebnisspalte. Das Programm hat auch schon funktioniert, jedoch habe ich vorher nur die spalten addiert und sortiert.

HIer mein bereits geschriebener Code:

Private Sub CommandButton1_Click()



' Prüfen ob Lastspitzen eingetragen wurden, ansonsten Fehlermeldung

        If TextBox1.Value = "" Or TextBox2.Value = "" Then
        MsgBox "Bitte geben Sie eine Lastspitze ein!"
     
Else
        
Dim wks As Excel.Worksheet
Dim lngLetzteZeile As Long
Dim Last1 As Integer
Dim Last2 As Integer
Last1 = TextBox1.Value
Last2 = TextBox2.Value  

Set wks = ActiveWorkbook.Worksheets("Vergleich")


With wks
 
lngLetzteZeile = .Cells(.Rows.Count, "B").End(xlUp).Row
If lngLetzteZeile < 15 Then Exit Sub

 With .Range("D15:D" & lngLetzteZeile)
 'berechne: D := B + C
 .FormulaR1C1 = "=RC[-2]*Last1+RC[-1]*Last2"
 'Formeln durch deren Ergebnis ersetzen
 .Value = .Value
 End With
 
With .Range("B15:D" & lngLetzteZeile)
 .Sort Key1:=.Cells(2), Order1:=xlDescending, _
 DataOption1:=xlSortTextAsNumbers, _
 SortMethod:=xlPinYin
End With

End With
End If


End Sub

...zum Beitrag

Excel - fortlaufende Zahlen

Wie heißt die Formel für fortlaufende Zahlen in einer Spalte, also immer untereinander in der gleichen Spalte: in der 1. Zeile die 1, in der zweiten Zeile die 2 usw. = Nummerierung der Zeilen? Danke.

...zum Beitrag

Per Excel VBA eine Zeile mit einer Spalte vergleichen :) Hilfe :O

Ich versuche gerade ein Programm zu schreiben das eine Zeile mit einer Spalte vergleicht. Nun ic bin gerade noch am Anfang des Programmes und nicht sehr erfolgreich. Das Prizip ist es die Zeile (in Tabelle2) mit der Spalte(bzw. erstmal mit der Zelle(in Tabelle1)zu vergleichen). Die Zeile in Tabelle1 wird so gebildet das A und i zsm eine zeile angeben und i als Variable für die Forschleife dient. Das heißt das Programm geht Zeile für Zeile durch und sucht nach einer Zahlenübereinstimmung. Falls was gefunden wurde soll er erstmal per MsgBox ein Simples Test ausgeben damit ich weiß das es Funktioniert. Ich muss zugeben das ich VBA erst vor 3 Tagen angefangen habe zu Lernen. Dehalb seid nicht so Streng mit Fehlern zu mir :D ich hoffe ihr konnte mich helfen mfg Dennis :)

Sub CommandButton1_Click()

Dim i1 As Integer

i = 1

For i = 1 To 100

If Tabelle1.Cells(1 & i) = Tabelle2.Cells(1, 1) Then

MsgBox "Test"

End If

Next i

End Sub

...zum Beitrag

Excel Zeilen abfragen und Wert in eine Andere Zeile einfügen aber wie?

Hallo, ich habe folgendes Problem, ich möchte die Zeile K nach einem bestimmten Wert abfragen, Zeile K hat so an die 100 Spalten.Nun wenn eine Spalte z.B. K3 den Wert 10 hat ,soll in Spalte L3 der Wert 1 stehen und das für jede Spalte.Alle Spalten in K die < als 10 sind sollen in den jeweiligen Spalten in L den Wert 2 bekommen.

Ich hoffe, jemand kann mir helfen. Danke im voraus.

...zum Beitrag

Was möchtest Du wissen?

Deine Frage stellen