Solving Circular Reference Formulas

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Cell A1 has a formula B1*7 and cell B2 has formula A1/7. So I have created
a circular reference! now, is there a way around this problem. What I like
to do is when a value is written on A1 the calculation appears in B1, but
when a value is added to B1 it would change A1. I'm more interested to see
if there is a solution to this type of problem more than the answer it
self.. I figure that many other calculations can be executed bases on this
idea.

Thank you
 
I assume the B2 in the first line should have been B1.

It doesn't seem possible for the solution as you envision, since
entering a number into A1 would delete the formula there, and likewise
for the cell B1.
 
Oh yea! B2 should be B1, sorry. One possible solution I found out may be by
using a VB macro here is what I came up with:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$1" And IsNumeric(Target) Then
Range("A1") = Range("B1") / 7
End If

If Target.Address = "$A$1" And IsNumeric(Target) Then
Range("B1") = Range("A1") * 7
End If
End Sub
I'm very new at this and understand very little about VB, just learning some
things from the people in this forum, so the formula may not be correct.
John what do you think about this? Is this look Ok to you.

Kevin Brenner
 
You should use the Worksheet_SelectionChange. My stab at it is:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address = "$B$1" Then
Range("A1").Value = Range("B1").Value / 7
ElseIf ActiveCell.Address = "$A$1" Then
Range("B1").Value = Range("A1").Value * 7
End If
End Sub

If you just did the Worksheet_Change, the macro loops, because the
entry you put in, say , A1, is a worksheet change, but what the macro
does to B1 as a result is also a worksheet change, so the macro has to
repeat again, and again, and again... The Worksheet_SelectionChange
limits the test to only the current selection, which is the active
cell where you make your input.

Putting .Value after the ranges is a good habit to have as it saves
VBA from trying to figure out what it's supposed to get out of the
Range code. Probably just saves a fraction of a millisecond, but
every little bit helps.

After all these years of writing VBA code, I still can't explain the
(ByVal Target As Excel.Range) part, though!
 
John,
You are right! when I try running my code in a workbook with more
calculation, I see that it takes 5 to 10 seconds for all the calculation to
occur and excel freezes for longer times at times. I originally testes the
code only in an empty page with values in cells A1 and B1.
The one you alter is much more efficient, but it suffer from a problem,
after you enter the data in cell A1 or B1 excel will not run the macro until
the cells A1 or B1 get selected.
Like I said before is not very important to solve this issue is that I
thought I could use this idea in a worksheet. As I have it now, I make the
calculation (days to hours) in separate cells and then enter the time in
hours in the entry box in cell A1.

Thanks for your input.
Kevin Brenner
 
Yes, I guess you've had to select A1 or B1. Your original code would
also need to do this. Another way is to have create a MsgBox with two
inputs, and depending on which input you enter (there has to be a way
to tell the code what to do if you have both inputs, though), the
macro writes in the appropriate cell. The nice thing about this is
that you can link the msgbox to pop up based on an accelerator key
(Ctrl+letter, for example) and you can be anywhere in the model to get
the answer written in either A1 or B1.

Kevin said:
John,
You are right! when I try running my code in a workbook with more
calculation, I see that it takes 5 to 10 seconds for all the calculation to
occur and excel freezes for longer times at times. I originally testes the
code only in an empty page with values in cells A1 and B1.
Click to expand...
 
Back
Top