keep value generated by formula

[pilotwings64]

I have a worksheet with a series of random numbers, but I want them to keep their values once another value is entered into a neighboring cell (as opposed to changing to a new random number every time I change any cell in the worksheet). Does anyone know of a function that can do this?

[Richard Buttrey]

I have a worksheet with a series of random numbers, but I want them to keep their values once another value is entered into a neighboring cell (as opposed to changing to a new random number every time I change any cell in the worksheet). Does anyone know of a function that can do this?

I don't know of a function, but you could achieve it with a VBA macro which fired on a Worksheet Selection Change event.

The macro would use the new value as the ActiveCell and copy the adjacent random number and paste it back as a value.

HTH

[pilotwings64]

I have not really gotten into VBA yet. What I need is something that interests me to be my first project. Can you give me the code for what you are talking about along with instructions about how to implement it?

[Richard Buttrey]

I have not really gotten into VBA yet. What I need is something that interests me to be my first project. Can you give me the code for what you are talking about along with instructions about how to implement it?

OK, here's the code. It assumes your random numbers are in column A, and the adjacent entry you make is in column B. The code will obviously need modifying if these are different.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim x As Integer

If Intersect(Target, Range("A:A")) Is Nothing Then
    x = ActiveCell.Offset(0, -1)
    ActiveCell.Offset(0, -1) = x
End If

End Sub

You need to go into the Visual Basic Environment (VBE), so press the ALT & F11 keys.

Hopefully you should see a window on the left called the Project Window. In there you should see references to your sheet names and workbook name.

Double click on the sheet where you have your random numbers, and you'll find another window opens on the right. At the top of this window are two drop down boxes. In the left hand one, select the 'Worksheet' object, and then in the drop down box on the right select the 'Change' event, and paste the code above between the

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
End Sub

code

Note the code above already has the Private....End Sub so don't duplicate it.

Press ALT-F11 again to close the VBE

Post back if you have any probs.

Rgds