Consulting

Results 1 to 7 of 7

Thread: Add between 10% to 20% random to numbers in cloumn F

  1. #1
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    404
    Location

    Add between 10% to 20% random to numbers in cloumn F

    Hi everyone
    I have a List that i need add Random between 10 to 20 Percent to the value are in Column F . and must ignore blank cell and cell has character only apply in cell with number .

    For Example If i have 13.5 in Column F , add 11 Percent (Between 10 to 20 Percent) will be 14.985 . Could you please help me on this .

    Appreciate for all you help and effort.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,235
    Is that the current inflation percentage each day?

  3. #3
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    404
    Location
    No , It is not just it will be run for one time and important to be random mean from 10 to 20 Percent .. .between these .. my list is about 94000 row .

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,949
    Location
    Fixed value and formula


    Option Explicit
    
    
    Sub FixedValue()
        Dim r As Range, c As Range
    
    
        Set r = ActiveSheet.Columns(6).SpecialCells(xlCellTypeConstants, xlNumbers)
    
    
        Randomize
    
    
        For Each c In r.Cells
    
            'fixed value
            'c.Offset(0, 1).Value = c.Value * (1.1 + 0.1 * Rnd)
    
            'formula
            c.Offset(0, 1).Formula = "=" & c.Address & "*(1.1+0.1*RAND())"
        
        Next
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    The result will be between 10.00000000000% and 20.99999999999% increase
    Option Explicit
    
    Sub AddToF()
    Dim FArr
    Dim i As Long
    
    FArr = Intersect(Range("F1").CurrentRegion, Range("F:F")).Value
    
    For i = 1 To UBound(FArr)
        If IsNumeric(FArr(i, 1)) Then _
            FArr(i, 1) = FArr(i, 1) * 1 + (Int((20 - 10 + 1) * Rnd + 10) / 100)
    Next i
    
    Range("F1").Resize(UBound(FArr, 1)) = FArr
    End Sub
    Example Returns, as percent additions:
    10.999999940395400%
    14.000000059604600%
    18.000000715255700%
    14.000000059604600%
    15.999999642372100%
    18.000000715255700%
    17.000000178813900%
    11.999999731779100%
    15.000000596046400%
    18.999999761581400%
    11.999999731779100%
    15.999999642372100%
    15.000000596046400%
    12.999999523162800%
    20.000000298023200%
    10.999999940395400%
    17.000000178813900%
    15.999999642372100%
    17.000000178813900%
    14.000000059604600%
    12.999999523162800%
    14.000000059604600%
    10.000000149011600%
    Last edited by SamT; 07-16-2021 at 10:00 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    copy and paste to New Module:
    Public Function fn1020pct(r As Range) As Variant
        Dim num As Double
        fn1020pct = ""
        If IsNumeric(r & "") Then
            num = CDec(RandBetween(10, 20)) / CDec(100)
            fn1020pct = r.Value * (1 + num)
        End If
    End Function
    
    
    
    
    Public Function RandBetween(Optional Lowest As Long = 1, Optional Highest As Long = 9999)
    ' Generates a random whole number within a given range
       Randomize (Timer)
       RandBetween = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
    End Function
    on the cell you want to apply the 10~20%, add this formula:

    =fn1020pct(F1)


    copy the formula down.

  7. #7
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    404
    Location
    Well Done and Thanks for your works and effort .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •