Macro help - Compare random numers and add

[Ellias_Adelle]

Hello,

I'm pretty new to working with macros, so I hope I'm sorry if this seems easy to you. It has been stumping me for days and I can't figure it out no matter how much research I do.

What I am trying to do is compare a random number between 1 and 100 (no decimals) to a value in a cell and if that value is above that number I want to permanently add a random number (no decimals) between 1 and 10 to that number.

For example, if the value is 70 I would like the macro to add a random number between 1 and 10 to it 30 percent of the time. Let's say the macro increases the number to 76. If activated again I want it to have a 24 percent chance of increasing the number again.

If there is an easier way to do this than creating a random number and comparing it to the value I'm fine with that. I so long as the results are the same.

I don't want to have a value for the random numbers be posted in the workbook.

 

[SQUIDD]

Hi, so i sort of follow what you are trying to do. I think.

But maybe not.

Because it looks like you want to simply use the 1 evolving number, but you said random number.

anyway, the below may help

Sub random()
Range("b1") = ""
Randomize
random100 = Int((100 - 1 + 1) * Rnd + 1) 'generate random number between 1-100
    If random100 > Range("a1") Then 'checks to see if the random number above is mare than cell a1
        my_percentage = 100 - random100 'works out what percentage of the time to increase the number by
        Randomize
        random_decision = Int((100 - 1 + 1) * Rnd + 1) 'picks another number between 1-100
            If random_decision <= my_percentage Then 'check to see if the new random number is less than or = to percentage
                Randomize
                random10 = Int((10 - 1 + 1) * Rnd + 1) 'creates a random number between 1-10
                my_number = random100 + random10 'adds them together
                Range("b1") = my_number 'outputs the number if all criteria was met
            End If
    End If
End Sub

 

[SQUIDD]

Hi

So i had a bit of fun with the code above and introduced some message boxes to explain whats happening as you run the code.

Still not sure if its what you wanted though, but it was a bit of fun writing this one.

Sub random()
Range("b1") = ""
Randomize
random100 = Int((100 - 1 + 1) * Rnd + 1) 'generate random number between 1-100
MsgBox ("random number is ") & random100
    If random100 > Range("a1") Then 'checks to see if the random number above is mare than cell a1
        MsgBox ("random number " & random100 & " is more than " & Range("a1") & " from cell a1")
        my_percentage = 100 - random100 'works out what percentage of the time to increase the number by
        MsgBox ("we have a " & my_percentage & "% chance of increasing this number")
        Randomize
        random_decision = Int((100 - 1 + 1) * Rnd + 1) 'picks another number between 1-100
            If random_decision <= my_percentage Then 'check to see if the new random number is less than or = to percentage
                Randomize
                random10 = Int((10 - 1 + 1) * Rnd + 1) 'creates a random number between 1-10
                my_number = random100 + random10 'adds them together
                Range("b1") = my_number 'outputs the number if all criteria was met
                MsgBox ("the number was increased from " & random100 & " by " & random10 & " making a new total of " & my_number)
            Else
            MsgBox ("the number did not get increased, end result was " & random100)
            End If
    Else
    MsgBox ("the random number " & random100 & " was below " & Range("a1") & " from cell a1, nothing happened and the end result was " & random100)
    End If
End Sub

 

[Ellias_Adelle]

Thank you so much! This isn't exactly what I was looking for but I appreciate the response so much! The miscommunication is entirely my fault for wording the question poorly. Let me try again.

Let's say that the value in A1 is 65. I would like to create a random number between 1 and 100 (to simulate rolling percentile dice). If the result is above the value in cell A1 (66+ in this case) I want to add a random number between 1 and 10 the value in cell A1.

If you don't want to or don't have time for more advise I appreciate the help you have given me. It would take a while, but I understand what I am trying to write a bit better now, so if I spent some real time messing around with the code I might be able to figure it out.

 

[Gokhan Aycan]

I wonder if this would do Heavily modified SQUIDD's code

A1 = Number to increase
B1 = Chance to increase by a random number from 1 to 10

Option Explicit

Sub random()

    Dim Chance_To_Increase As Integer
    Dim Number_To_Increase As Integer
    Dim Random100 As Integer
    Dim Increase_By As Integer
    Dim Random10 As Integer

    Chance_To_Increase = IIf(Range("B3").Value = "", Range("B1").Value, Range("A3").Offset(Range("A3").CurrentRegion.Rows.Count - 1, 1).Value)
    
    Number_To_Increase = IIf(Range("A3").Value = "", Range("A1").Value, Range("A3").Offset(Range("A3").CurrentRegion.Rows.Count - 1, 0).Value)
    
    Randomize
    
    Random100 = Int((100 - 1 + 1) * Rnd + 1) 'generate random number between 1-100
    
    If Random100 > Number_To_Increase Then 'checks to see if the random number above is mare than cell a1
        
        Randomize
        
        Random10 = Int((100 - 1 + 1) * Rnd + 1)
        
        If Random10 <= Chance_To_Increase Then
            
            Randomize
            
            Increase_By = Int((10 - 1 + 1) * Rnd + 1)
            
            If Chance_To_Increase - Increase_By < 0 Then Increase_By = Chance_To_Increase
            
            If Range("A3").Value = "" Then
                Range("A3").Value = Number_To_Increase + Increase_By
                Range("B3").Value = Chance_To_Increase - Increase_By
            Else
                Range("A3").Offset(Range("A3").CurrentRegion.Rows.Count, 0).Value = Number_To_Increase + Increase_By
                Range("A3").Offset(Range("A3").CurrentRegion.Rows.Count - 1, 1).Value = Chance_To_Increase - Increase_By
            End If
            
        End If
        
    End If
    
End Sub

 

[SQUIDD]

ok so i would say reading your second description only in post 4, the code below is correct.
However, i am sure this now has something missing since this is very hevily simplified comparred to post 1.

But like i said, if you read my comments in the code, this deos exactly as per the description.

Sub random()
Randomize
random100 = Int((100 - 1 + 1) * Rnd + 1) 'generate random number between 1-100
    If random100 > Range("a1") Then 'checks to see if the random number above is more than cell a1
                random10 = Int((10 - 1 + 1) * Rnd + 1) 'creates a random number between 1-10
                Range("a1") = random100 + random10 'outputs the number after addition if criteria was met
    End If
End Sub

 

[Ellias_Adelle]

Thank you so much! SQUIDD, this most recent one is so close to correct. I thought I could complete it from there, but I'm running into one last problem. Right now it is set up so that if the macro is run and the conditions are met it outputs a random number between 1 and 100 and adds that to a random number between 1 and 10 (as far as I can tell). What I want it to do is add a random number between 1 and 10 to the value in cell a1. I have tried a number of things and I'm so close. I feel like this is the easiest problem possible, but I'm not sure what I'm doing wrong.

I'm trying
Range("a1") = ("a1") + random10

Can you help me figure out what I'm doing wrong?

 

[Gokhan Aycan]

Correct syntax is

Range("a1") = Range("a1") + random10

 

[Ellias_Adelle]

Thank you so much! It's working perfectly now! You guys are all amazing. I don't know why this is so hard for me. I'll keep working on it!

 

[SQUIDD]

Hey.
Glad it’s all working for you.
Your welcome. ??