VBA - Zufall

Moderator: ModerationP

VBA - Zufall

Beitragvon Gast » 01. Dez 2020, 20:05

Hallo!

Ich habe mehrere Probleme und möchte die euch schildern, da ich mir sicher bin ihr könnt mir dabei helfen diese zu lösen.

Der Tabellenaufbau ist absolut simpel und besteht eigentlich nur aus 2 relevanten Spalten:
- Spalte B enthält neben der Überschrift darunter eine unbekannte Anzahl an Wörtern
- Spalte D enthält neben der Überschrift darunter genausoviele Zeilen gefüllt mit Zahlen zwischen 1 und 6
- Die Daten beider Spalten enden in der selben Zeile

1. Per Zufall möchte ich eine Range in Spalte B auswählen lassen. Mit dem zusammengewürfelten Code "Zufallszahl" lasse ich eine auswählen, gibt mir in der Schleife auch 10 mal Werte aus.

Soweit, so gut, aber leider bekomme ich in der Schleife auch die Zelle B1 als Ergebnis, was ich aber unbedingt ausschließen möchte, da dort nur die Überschrift steht!
(die MsgBox in der Schleife soll mir nur zum Test anzeigen welche Zelle ausgewählt wurde, die Schleife habe ich wiederum nur eingebaut, dass ich gleich mehrere Ergebnisse sehe)

Was muss ich im Code ändern / hinzufügen, damit ich B1 nicht als Ergebnis bekommen kann?

Code: Alles auswählen
Sub Zufallszahl()
Dim strZufall As String
For i = 1 To 10
strZufall = "B" & Int((Cells(Rows.Count, 2).End(xlUp).Row - 1) * Rnd + 1) * 1
MsgBox zufall
Next
End Sub


Jetzt sollte aber auch noch die erwähnte Spalte D ins Spiel kommen,
denn ich hätte gerne nur Ergebnisse in Spalte B, wo in der selben Zeile in Spalte D eine andere Zahl als 1 steht!.

Steht also in der dritten Zeile in Spalte D eine 5, so darf die B3 als Ergebnis kommen,
steht aber in der vierten Zeile in Spalte D eine 1, so darf die B4 NICHT als Ergebnis vorkommen.

Wie gesagt, der Tabellenaufbau ist simpel, da nur zwei relevante Spalten!

Den Code Zufallszahl verstehe ich soweit noch abgesehen von dem Problem mit der Kopfzeile, aber das Einbinden von Spalte D, da fehlt mir alles.

Ersuche höflichst um Unterstützung!

Danke.
Gast
 

Re: VBA - Zufall

Beitragvon AlterDresdner » 01. Dez 2020, 21:07

Hallo,
so sollte es gehen:
Code: Alles auswählen
Sub Zufallszahl()
Dim strZufall As Long
Randomize
Do
  strZufall = Int((Cells(Rows.Count, 2).End(xlUp).Row - 1) * Rnd + 2)'mit der 2 wird 1 ausgeschlossen
Loop Until Val(Cells(strZufall, 4)) <> 1
Cells(strZufall, 2).Select
End Sub
Gruß der AlteDresdner
(Win 10 32bit, Off2010)
AlterDresdner
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 113
Registriert: 24. Okt 2015, 15:57

Re: VBA - Zufall

Beitragvon DerHoepp » 01. Dez 2020, 21:19

Moin,

mit einer dritten Spalte, in der du als Formel =ZUFALLSZAHL() kannst du über KKLeinste einfach die sechs kleinsten Zufallswerte auslesen und benötigst überhaupt kein VBA. Dadurch schließst du gleichzeitig auch doppelte Zufallswerte ziemlich sicher aus.

Viele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9229
Registriert: 14. Mai 2013, 11:08

Re: VBA - Zufall

Beitragvon Gast » 01. Dez 2020, 22:00

Hallo AlterDresdner!

Danke, das ging ja schnell!!

Und vor allem sehr kompakt.
ich danke dir vielmals für die Hilfe,

Natürlich kommt von mir gleich wieder eine Zusatzfrage, mein finaler Code sieht dank deiner Hilfe jetzt wie folgt aus:

Code: Alles auswählen
Sub zufallszahl()
'
For i = 1 To 10
Dim strZufall As Long
Randomize
Do
  strZufall = Int((Cells(Rows.Count, 2).End(xlUp).Row - 1) * Rnd + 2) 'mit der 2 wird 1 ausgeschlossen
Loop Until Val(Cells(strZufall, 4)) <> 1
Cells(strZufall, 2).Select

MsgBox ActiveCell.AddressLocal
Next
End Sub


Die Zusatzfrage: Wenn jetzt per Zufall B2 ausgewählt wird, sollte in Spalte D die Zahl um eins reduziert werden.

Danke dir bisher
Gast
 

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 06:50

ok, bin selbst drauf gekommen.

hab noch folgendes eingefügt

Code: Alles auswählen
Cells(strZufall, 4).Activate
ActiveCell.Value = ActiveCell.Value - 1
Gast
 

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 06:53

Das Problem, welches jetzt aufgetaucht ist, dass er sich natürlich ohne die Schleife irgendwann selbst "aufhängt"!

Sobald alles in Spalte D auf 1 steht, sollte er aufhören zufällig in Spalte B auszuwählen.
Könnte man das noch elegant lösen?
Gast
 

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 09:39

sorry, natürlich hilft euch sicherlich bei der Unterstützung der aktuelle Code.

Code: Alles auswählen
Sub zufallszahl()
'
For i = 1 To 20
Dim strZufall As Long
Randomize
Do
  strZufall = Int((Cells(Rows.Count, 2).End(xlUp).Row - 1) * Rnd + 2) 'mit der 2 wird 1 ausgeschlossen
Loop Until Val(Cells(strZufall, 4)) <> 1
Cells(strZufall, 2).Select
'MsgBox ActiveCell.AddressLocal
Cells(strZufall, 4).Activate
ActiveCell.Value = ActiveCell.Value - 1
Next
End Sub
Gast
 

Re: VBA - Zufall

Beitragvon snb » 02. Dez 2020, 10:09

Code: Alles auswählen
Sub M_snb()
   With Columns(2).SpecialCells(2).Offset(1).SpecialCells(2)
        sn = .Value
        .Offset(, 400) = "=rand()"
        sp = Evaluate("index(rank(" & .Offset(, 400).Address & "," & .Offset(, 400).Address & "),)")
    End With
   
    For j = 1 To UBound(sp)
       sp(j, 1) = sn(sp(j, 1), 1)
    Next
End Sub
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8191
Registriert: 25. Sep 2014, 16:37

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 12:09

habe eher daran gedacht, den Loop Until zu ändern.

Wenn ich in E1 den Mittelwert berechne und der größer als 1 ist, dann könnte das doch ein Parameter sein.

funktioniert aber leider nicht, wenn ich es so mache.


Code: Alles auswählen
Loop Until Val(Cells(strZufall, 4)) <> 1 Or Val(Cells(1, 5)) > 1
Gast
 

Re: VBA - Zufall

Beitragvon DerHoepp » 02. Dez 2020, 13:42

Hi,

wenn du statt mit nicht-funktionierendem Code in ganzen Sätzen mit uns sprichst und dabei erläuterst, was du eigentlich erreichen willst (Zufallsauswahl aus einer Liste von Datensätzen?) und warum du glaubst, dass VBA eine gute Lösung ist, fällt uns das Helfen wahrscheinlich einfacher. Im Idealfall stellst du auch noch eine Beispieldatei ein.

VIele Grüße
derHöpp
DerHoepp
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 9229
Registriert: 14. Mai 2013, 11:08

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 14:44

Hallo,

ich habe bisher gedacht, dass ich klare Sätze formuliert habe, auch die Einfachheit der Spalten dargestellt habe.
Es hat sich jedoch herausgestellt, das der vorliegende, funktionierende Code eine Schwachstelle hat.

In Spalte B werden nur solange Ziele zufällig ausgewählt, solange in Spalte D eine Zahl größer 1 steht.
Sobald aber überall in Spalte D eine 1 steht, hängst sich Excel auf, die Abhängigkeit hätte ich gerne noch eingebaut.

Ich kann nicht auf Formeln zurückgreifen, da im großen Ganzen hier das ein Teil eine ausführlichen Makros ist für einen Vokabeltrainer.
Konkret habe ich da an die Zeile "Loop Until" gedacht, dass man hier noch den Abbruch einpflegt.

Code: Alles auswählen
Sub zufallszahl()
'
For i = 1 To 20
Dim strZufall As Long
Randomize
Do
  strZufall = Int((Cells(Rows.Count, 2).End(xlUp).Row - 1) * Rnd + 2) 'mit der 2 wird 1 ausgeschlossen
Loop Until Val(Cells(strZufall, 4)) <> 1
Cells(strZufall, 2).Select
'MsgBox ActiveCell.AddressLocal
Cells(strZufall, 4).Activate
ActiveCell.Value = ActiveCell.Value - 1
Next
Gast
 

Re: VBA - Zufall

Beitragvon slowboarder » 02. Dez 2020, 15:23

Hi

wenn du zufällige Ganzzahlen brauchst (Zeilennummern), dann verwende:
Code: Alles auswählen
Worksheetfunction.RandBetween(kleinste Zahl, größte Zahl)

hat folgende Vorteile:
1. das Formelwerk ist einfacher und die Grenzen können direkt eingegeben werden
2. du brauchst kein Randomize, um unterschiedliche Zufallszahlen zu bekommen (wobei ich nicht sicher bin, ob Randomize mit immer dem selben Startwert nicht auch nach jedem Öffnen die selbe Zahlenreihe für RND liefert)

um die Endlosschleife zu vermeiden kannst du doch einfach abfragen, ob es Werte gibt die nicht 1 sind:

Code: Alles auswählen
If Worksheetfunction.CountIf(Range("D:D"), ">1") > 0 Then
    For i = 1 to 20
        Do
            strZufall = Worksheetfunction.RandBetween(2, Cells(Rows.count, 2).End(xlup).row)
        Loop Until Val(Cells(strZufall, 4)) <> 1
       ….
    next
else
    hier der Code der passieren soll, wenn alles 1 ist
End if


btw das Präfix "str" in Variablennamen wird normalerweise verwendet, um anzuzeigen das die Variable vom Typ String ist.
Warum verwendest du das "str" für eine Zahl-Variable (Long)

btw es ist nicht notwendig, eine Variable mehrfach innerhalb einer Schleife zu dimensionieren.
normalerweise reicht einmal am Anfang.

dieses Dimensionieren innerhalb des Codes macht man eigentlich nur bei Objektvariablen- oder Variantvariablen, die man komplett leeren will (zustand noch kein Wert zugewiesen).
ansonsten ist das unsinn.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 27553
Registriert: 18. Apr 2009, 13:33

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 15:34

oh, Danke!

das str ist historisch falsch gewachsen.
Im ersten Beitrag hatte ich es noch als String formatiert, dann aber auf Long :-)

Danke für deine Hilfe!
Gast
 

Re: VBA - Zufall

Beitragvon Der Steuerfuzzi » 02. Dez 2020, 16:34

slowboarder hat geschrieben:
Code: Alles auswählen
Worksheetfunction.RandBetween(kleinste Zahl, größte Zahl)

Diese Funktion hat noch einen weiteren (möglicherweise erheblichen) Vorteil: Sie verwendet einen besseren Pseudo-Zufallszahlen-Algotithmus und liefert damit wesentlich bessere Zufallszahlen als randomize/rnd.
Gruß
Michael
Benutzeravatar
Der Steuerfuzzi
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 3827
Registriert: 25. Mär 2013, 13:28

Re: VBA - Zufall

Beitragvon Gast » 02. Dez 2020, 17:32

Danke Steuerfuzzi,

hab ich sofort umgesetzt, läuft einwandfrei!

Ich danke dir!
lg
Gast
 

Nächste

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Rudius und 9 Gäste