Excel Random Number Generator 1

[Brianfree]

Hi, I have an excel spread sheet which looks something like this..

OEM, Description, Code, Qty
abc, 123456, 111, 22
def, 234567, 112, 56
ghi, 234567, 111, 12

I need to be able to modify the quantity depending on a rule set against the Code..

For example...

If the code is 111 and the quantity is > 50 then pick a random number between 40 & 49 else leave the value alone.
If the code is 112 and the quantity is > 200 then pick a random number between 190 & 199 else leave the value alone.

Please can anyone advise?

Many thanks,

Brian

 

[BTilson]

Something like this should work, although it may need to be tweaked slightly to fit your needs.

=IF(C1=111,IF(D1>50,RANDBETWEEN(40,49)))

This is written assuming your data starts in cell A1. Take this same line and modify the values to get your second request.

Hope that helps!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[SkipVought]

1) the OP has a heading row

2) the original qty must be returned if the criteria is not met
[tt]
E2: =IF(AND(C2=111,D2>50),RANDBETWEEN(40,49),IF(AND(C2=112,D2>200),RANDBETWEEN(190,199),D2))
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

HI, thanks for your replys.. I have got this working with a formula but would like to do this with a macro...

here is my code..

=IF(AND(LEFT(C2,3)="100",F2>100),RANDBETWEEN(90,100),
IF(AND(LEFT(C2,3)="101",F2>20),RANDBETWEEN(15,20),
IF(AND(LEFT(C2,3)="114",F2>120),RANDBETWEEN(110,120),
IF(AND(LEFT(C2,3)="115",F2>20),RANDBETWEEN(15,20),
IF(AND(LEFT(C2,3)="116",F2>80),RANDBETWEEN(70,80),
IF(AND(LEFT(C2,3)="117",F2>20),RANDBETWEEN(15,20),F2))))))

I would like to paste my data into a template and just run a macro instead of copy/paste formula etc..

Is there a simple way to do this?

Many thanks

Brian

 

[SkipVought]

What code have you tried so far and where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

Hi, thanks for replying.. My test program with different values was like the following but Im not sure how to translate a formula to a macro??

Sub Process_Stocklist()

    Dim X           As Long
    Dim Y           As Long
    Dim intX        As Long
    
    Y = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    For X = 2 To Y
        
        With Range("E" & X).Select
                 
             If ActiveCell.Text = "100*" Then
                With Range("D" & X)
                    If .Value > 49 Then
                      .Value = Int((49 - 40 + 1) * Rnd + 40)
                    End If
                
             ElseIf ActiveCell.Text = "200*" Then
                With Range("D" & X)
                    If .Value > 49 Then
                      .Value = Int((149 - 140 + 1) * Rnd + 140)
                    End If

             End If

        End With
    Next
     

End Sub

Many thanks

Brian

 

[SkipVought]

???

The values you are using in your macro bares but a faint resemblence to the formula you posted.

What goes? I am comfused!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

Hi, my test program was just for quickness - to see if I can get a macro to do something.. if I could... I was going to use the correct values that I have in the formula?

Sorry for being a pain.

Many thanks.

Brian

 

[SkipVought]

Why bother with something fake when you could test with something actual?

Help yourself out!

Lets see some personal initiative!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

Thanks for being honest.. Will have a go and post my "working result" later.

Many thanks

Alan

 

[SkipVought]

1. put your data in a table, rather than data in your code. My Structured Table is named tRAND
[tt]
C F FROM THRU
100 100 90 100
101 20 15 20
114 120 110 120
115 20 15 20
116 80 70 80
117 20 15 20

[/tt]

Sub Process_Stocklist()

    Dim X           As Long
    Dim Y           As Long
    Dim intX        As Long
    Dim aRAND, i As Integer
    
    Const C_ = 1
    Const F_ = 2
    Const FROM_ = 3
    Const THRU_ = 4
    
    aRAND = [tRAND]
    
    Y = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    
    For X = 2 To Y
        
        If Left(Range("C" & X), 3) = aRAND(1, C_) And Range("F" & X) > aRAND(1, F_) Then
            Range("E" & X) = aRAND(1, FROM_) + Rnd * (aRAND(1, THRU_) - aRAND(1, FROM_))
        End If
    Next
     

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}