I have cells that contain information that changes with every change to another cell, and instead of using "=A1" and linking the data straight to that cell I want it to contain the answer to the formula. Example: Say "A1" contains "=INT(RAND()*((9+1)-2)+2)" (Which is a random number from 2~9) After putting that in it would generate a random number, but this particular number is not needed. Because the next change I make to get that random number would first change it's number, and then take it. I don't want to use "=A1" as said before, because it would just copy the same number as well as when it changes. I just want it to take the number and not change, while A1 still continues to generate a random number from 2~9. Note: Creating a circular reference works, but it has 2 problems. 1) It creates a pop-up (which can probably be removed through a macro -- which is what most of this is going to be done in anyways) 2) It requires 2 cells PLUS the one containing the changing information.

Why not generate the random number in VBA and then place it in A1 - or wherever you really need it? The value would only change when the macro was run, over which you would have more control. Sub RandNum() Dim LRandomNumber As Integer Randomize LRandomNumber = Int((9 - 2 + 1) * Rnd + 2) Range("A1") = LRandomNumber End Sub Perhaps use the code above in a Change event so the macro would run automatically when you make a change that would cause to you want a new random number. Private Sub Worksheet_Change(ByVal Target As Range) Dim LRandomNumber As Integer If Target.Address = "$D$1" Then Randomize LRandomNumber = Int((9 - 2 + 1) * Rnd + 2) Range("A1") = LRandomNumber End If End Sub

hmm, that looks like a good idea, but, what if the changing number was dependent on other cells ie: =INT(RAND()*((B1+1)-A1)+A1) could it be done with variables in VBA which represent those cells? if so, how could I code that, because I don't know much about VBA

"could it be done with variables in VBA which represent those cells?" Yes "if so, how could I code that, because I don't know much about VBA" In your first post you said: "1) It creates a pop-up (which can probably be removed through a macro -- which is what most of this is going to be done in anyways)" How are you going to do "most of this" in a macro if you don't know much about VBA? In any case, yes, you can use variables in VBA to represent the ranges: Private Sub Worksheet_Change(ByVal Target As Range) Dim LRandomNumber As Integer If Target.Address = "$D$1" Then RndVar1 = Range("A1") RndVar1 = Range("B1") Randomize LRandomNumber = Int((RndVar2 - RndVar1 + 1) * Rnd + RndVar1) Range("A2") = LRandomNumber End If End Sub

I just usually record macros. :P

Recording macros is a great way to learn how to write code, but typically results in bloated and inefficient code. Once you record a macro, use F8 in the VBA editor to Single Step thorugh the code to see what it is doing. Once you see how the code flows, you should be able to eliminate alot of the extra stuff the macro recorder puts in. For example, if you record the selection of D13 and enter 43 in the cell, you'll get this: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 07/30/2008 by Me ' ' Range("D13").Select ActiveCell.FormulaR1C1 = "43" End Sub That can be reduced to: Sub Macro1() Range("D13")= 43 End Sub You also can't record Loops or If-Then's or any of the other things that will make your VBA code a lot more efficient.

then you should teach me some of these wondrous things :P