Fixed Random Function

**mycon73** · 02-26-2011, 03:53 PM

Hi,

I have a template in which I'm using a random function to generate (or make up) hours for various projects worked on during specific start & finish dates. Within these projects, I also have 5 primary sub-tasks with handful of secondary tasks.

The function I'm using is:

=RAND()*$C$6*100

$C$6 is pointing to a specified percentage, in this instance 12%

The problem with my worksheet is now when I edit other areas, my table is randomizing.

Is there a way to "fix" or 'lock" the randomization, once I have desirable numbers in place? If so, how?

Thanks

**shg** · 02-26-2011, 04:07 PM

You'd need to copy the formulas and paste as values.

**mycon73** · 02-26-2011, 06:49 PM

Hi,

I attached an Excel 2007 file for questioning.

When reviewing the file, you will see Colums I - R, Rows 27 - 39. In the task areas, I have bunch of random numbers that were generated Estimated Min. Max. hours found in Column D & E & its respective rows.

This section seems to work well.

Now, I'm questioning when I edit other areas, such as items found above Row 26, my numbers are being re-randomized.

Why is this?
More importantly, how can randomize desired sections - Below Row 26 & then lock it, so it does not modify other calculations?

Thanks

**e4excel** · 02-27-2011, 02:21 AM

mycon,

You need to have the RandomValues stored somewhere othan the present cells and then paste them as values from there so you have a FIxed Desired Random Nos and they dont get re-calculated..

Just create a Mirro of the same sheet with the Random Funtionality intact and record a macro of copying the values from the desired cells to the actual location..so whenver you need new values just run the Macro..

Warm Regards
e4excel

**mycon73** · 02-27-2011, 11:48 AM

Hi e4excel,

I had a feeling I would have to do this. I was hoping to avoid this because I'm still creating the worksheet & wanted to minimize having similar worksheets.

I've seen this done before, but not familiar on how to do this. I've seen some worksheets that has a macro icon to "copy" data from other worksheets.

If I were to do something like this, will the "copied" information come over as values or as the Random formula being used?

Also, how do I create a "Copy" macro to get desirable results - In this case, just the estimated randomized hours values to its appropriate cells? I have 5 primary Task sessions with a handful of sub-tasks.

Thanks

**e4excel** · 02-28-2011, 05:39 AM

MyCon,

I have replicated the Sheet and Renamed it as "Actual Formula Cells" meaning that the formula is in this Sheet and I have kep your Sheet as it is without Renaming it..

Now If you press the key Combination of CTRL+SHIFT+H then the Range of cells get copied into you Actual Sheet as values...and it does not get affected on F9 Evaluation ..

SO please check the Worksheet..

I am not good in VBA myself but trying just like you to get somewhere

I have not witten the code but simply recorded it...!

Sub CopyAsValues()
'
' CopyAsValues Macro
' e4excel for My Con
'
' Keyboard Shortcut: Ctrl+Shift+H
' Range("I28:R32").Select
    Selection.Copy
    Sheets("Experience Documentation-Random").Select
    Range("I28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Actual Formula Cells").Select
    Application.CutCopyMode = False
    Range("I34:R39").Select
    Selection.Copy
    Sheets("Experience Documentation-Random").Select
    Range("I34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Warm Regards
e4excel

**mycon73** · 02-28-2011, 01:04 PM

Hi e4excel,

Thank you very much for your assistance & the file. I will be checking it out shortly.

**e4excel** · 02-28-2011, 01:10 PM

Your Welcome...

Hope it helps!

**mycon73** · 03-04-2011, 01:40 AM

Hi e4excel,

I finally had a chance to check out your file. Looks like you created a copy function that removes the formulas.

I suppose this works. After working with my file some more, it appears that some of these randomness is excessively exceeding my project durations. Now, I need to figure out on how to resolve this issue.

Thanks for the help.

**e4excel** · 03-04-2011, 02:29 AM

MYcon

I didnt create anything just recorded the Sequence of steps which get played when you press the CTRL+SHIFT+H key combination..

As far as the Randomness is concerened that I suppose is at the conceptual lvel for you to decide..
BUt for getting the Randome Values I think this would be good enough..

REgards
e