418 Aufrufe
Gefragt in Tabellenkalkulation von letty19 Einsteiger_in (88 Punkte)
Hallo Leute

Könnte mir bitte jemand mit einem Makro helfen

Möchte gerne in Blatt A einen bestimmten Bereich von D2-H20 die mit Positiven und Negativen Zahlen gefüllt sind nur die positiven Zahlen von 0-45 auslesen
Und diese dann in Blatt B ab Position A 5 Horizontal einsetzten

Kann man das so umsetzten?

18 Antworten

0 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

Hallo letty,

Klar kann man das. Ist sogar recht simpel. Du brauchst nur eine Schleife mit der du jede Zelle des Bereichs durchgehst. Darin baust du eine If-Bedingung ein in der du prüfst ob sie einen gewünschten Wert enthält. Wenn Ja wird dann ein Zähler hochgesetzt der im zweiten Tabellenblatt die Spalte angibt. Probier mal:

 For Each c In Sheets("Blatt A").Range("D2:H20").Cells
   If c >= 0 And c <= 45 Then
      i = i + 1
      Sheets("Blatt B").Cells(5, i) = c
   End If
 Next c

Gruß Mr. K.

0 Punkte
Beantwortet von letty19 Einsteiger_in (88 Punkte)

Hi xlking

 funktioniert soweit aber kannst du mir bitte noch was anpassen

In meinem Suchbereich hab ich auch leere Zellen und doppelte Zahlen das genügt wenn er die Zahl 1x anzeigt

Auch die leeren Zellen kann man weglassen

Wenn man die Zellen mit den Zahlen noch einfärben könnte wäre das auch Super

Danke für deine Hilfe

0 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

Hallo letty,

Auch das ist kein Problem, man muss nur die bestehende Bedingung ein wenig erweitern.

Sub Auslesen()
Dim c As Range, Quelle As Worksheet, Ziel As Worksheet, i As Long, x As Long
Set Quelle = Sheets("Blatt A")
Set Ziel = Sheets("Blatt B")

 For Each c In Quelle.Range("D2:H20").Cells
   x = Application.WorksheetFunction.CountIf(Ziel.Rows(5), c.Value)
   If c <> "" And c >= 0 And c <= 45 And x = 0 Then
      i = i + 1
      Ziel.Cells(5, i) = c
      c.Interior.Color = RGB(0, 255, 0)
   End If
Next c
End Sub

Gruß Mr. K.

0 Punkte
Beantwortet von letty19 Einsteiger_in (88 Punkte)

Hi xlking

das einfärben der Zahlen funktioniert irgendwie nicht ansonsten passts

Danke Vielmals

Sag sollte ich mir mal eine Userform bauen kann ich das Makro dann Teilweise übernehmen

und mir die Zahlen dort anzeigen lassen dann müßte ich auch nichts einfärben

aber das ist jetzt Luxus...nur so zur Info

0 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

das einfärben der Zahlen funktioniert irgendwie nicht...

Bist du sicher, dass du keinen Monochromen-Monitor besitzt oder irgendwie die Farbe rausgedreht hast? Scherz beiseite: Das kann ich mir echt nicht erklären, warum das bei dir nicht funktioniert. Denn für das Einfärben ist der Befehl c.Interior.Color zuständig, der hier über die RGB Funktion im Sheet A bei allen in Frage kommenden Zahlen die Farbe hellgrün zuweist. Bei mir funktioniert das super. Du kannst anstatt der Color natürlich auch die ColorIndex-Eigenschaft nutzen und einen Wert zwischen 1 und 52 zuweisen. Aber die ist veraltet und sollte eigentlich nicht mehr genutzt werden.

Zu deiner Frage: Ja du kannst diesen Code (abgesehen von Sub und End Sub natürlich) auch im CommandButton_Click-Ereignis eines Userforms ausführen. Wenn du dem Userform eine Listbox zuweist, kannst du innerhalb der Schleife mit ListBox1.AddItem c alle gewünschten Werte hinzufügen.

Gruß Mr. K.

0 Punkte
Beantwortet von letty19 Einsteiger_in (88 Punkte)
haha xlking
bist ein Scherzkeks...passt schon du hast mir mit dem Makro sehr geholfen
einfärben ist nicht so wichtig
bin dir sehr dankbar

Letzte Frage
x = Application.WorksheetFunction.CountIf(Ziel.Rows(5), c.Value)
(Ziel.Rows(5)c.Value
kannst du mir das bitte erklären??
Was badeutet das würde das gerne verstehen
0 Punkte
Beantwortet von letty19 Einsteiger_in (88 Punkte)

hi

jetzt müßte ich doch noch was fragen

Sub Auslesen()
Dim c As Range, Quelle As Worksheet, Ziel As Worksheet, i As Long, x As Long
Set Quelle = Sheets("Blatt A")
Set Ziel = Sheets("Blatt B")

 For Each c In Quelle.Range("D2:H20").Cells
   x = Application.WorksheetFunction.CountIf(Ziel.Rows(5), c.Value)
   If c <> "" And c >= 0 And c <= 45 And x = 0 Then
      i = i + 1
      Ziel.Cells(5, i) = c
      c.Interior.Color = RGB(0, 255, 0)
   End If
Next c
End Sub

wie würde der code aussehen wenn ich in diesem Bereich alle doppelten Zahlen löschen wollte???
0 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

Zu deiner ersten Frage gibts nicht viel zu erklären. Man kann in VBA auch Excel-Eigene Funktionen verwenden. Hier die Formel Zählenwenn() Diese hat zwei Parameter. Im ersten wird der zu durchsuchende Bereich angegeben, (Hier die Zeile 5 auf dem Zieltabellenblatt), im zweiten der Wert nach dem gesucht wird (hier die der Wert der Zelle c). Die Tabellenblätter haben wir am Beginn des Makros mit Set den Variablen Quelle und Ziel zugewiesen. Mit For Each c In Quelle.Range().Cells durchlaufen wir jede einzelne Zelle im Quellbereich, wobei bei jedem Schleifendurchgang eine andere Zelle der Variablen c zugeordnet ist. Das Ergebnis der Funktion wird dann in die Variable x geschrieben. Nur wenn x=0, also wenn die Formel Zählenwenn() die gesuchte Zahl noch nicht im Zielbereich gefunden hat, wird die Bedingung ausgeführt und die Zahl in den Zielbereich geschrieben.

Wenn du die Zahlen nicht mehr in den Zielbereich schreiben willst, sondern z.B. in eine Listbox einer Userform klappt das mit dieser Funktion natürlich nicht mehr. Dann musst du dir was anderes überlegen, wie du das abprüfst. Man könnte z.B. prüfen ob die Zahl in einem String vorhanden ist. Oder man schreibt sie in ein Array, Letzteres hätte den Vorteil, dass du die Zahlen gleich sortiert ausgeben kannst, falls du das möchtest. Bevor ich dir hier aber Beispiele aufzeigen kann, muss ich schon wissen, was du eigentlich wie ausgeben möchtest.

Die einfachste Antwort zu deiner zweiten Frage: Ersetze End If durch:

Else
  c.ClearContents
End If

Dann werden allerdings nicht nur die interessanten doppelten Werte gelöscht sondern auch alle uninteressanten Werte, sodass nur die erstbesten interessanten Werte übrigbleiben. Ist es das was du erreichen willst? Dann kannst du dir zusätzliche Ausgaben an anderer Stelle ganz Sparen und weist sofort welche Zahl an welcher Stelle steht.

Gruß Mr. K.

0 Punkte
Beantwortet von letty19 Einsteiger_in (88 Punkte)

Danke für die Erklärung....

.....will das nur die Zahlen stehen bleiben die 1x vorkommen der rest kann gelöscht werden

Else
  c.ClearContents
End If

funktioniert leider nicht
er schreibt Laufzeitfehler
Objekt erforderlich
0 Punkte
Beantwortet von xlking Experte (1.5k Punkte)

Doch das funktioniert! Wenn ich schreibe, du sollst eine Zeile durch die neuen Zeilen ersetzen brauchst du das doch nur zu machen. Hier nochmal der Ganze Code:

Sub Auslesen()
Dim c As Range, Quelle As Worksheet, Ziel As Worksheet, i As Long, x As Long
Set Quelle = Sheets("Blatt A")
Set Ziel = Sheets("Blatt B")

 For Each c In Quelle.Range("D2:H20").Cells
   x = Application.WorksheetFunction.CountIf(Ziel.Rows(5), c.Value)
   If c <> "" And c >= 0 And c <= 45 And x = 0 Then
      i = i + 1
      Ziel.Cells(5, i) = c
      c.Interior.Color = RGB(0, 255, 0)
   Else
      c.ClearContents
   End If
  Next c
End Sub

Gruß Mr. K.

...