Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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. "Kevin" ! wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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 "John Tjia" wrote in message om... 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. "Kevin" ! wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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! "Kevin" ! wrote in message ... 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 "John Tjia" wrote in message om... 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. "Kevin" ! wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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 "John Tjia" wrote in message om... 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! "Kevin" ! wrote in message ... 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 "John Tjia" wrote in message om... 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. "Kevin" ! wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solving Circular Reference Formulas
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" ! wrote in message ... 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 "John Tjia" wrote in message om... 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! "Kevin" ! wrote in message ... 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 "John Tjia" wrote in message om... 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. "Kevin" ! wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Discussion (Misc queries) | |||
Circular Reference... Help! | Excel Worksheet Functions |