Solved: circular references and entering code back into cells

[sunilmulay]

Hi Guys
Please see the attached sample file.
Data in columns G, H and I all have formulae that refer to each other (circular references). I want the user to be able to choose which two columns they enter data into, and the third column is then calculated automatically.

This part works fine. I then wanted the formula to be re-entered into the cells, should the user delete the values they entered. I've pieced together the following code from a kind gentleman that helped me before on a similar issue, and this appears to work well too.

[vba]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tRow As Long
On Error GoTo endo

Application.EnableEvents = False

If Not Intersect(Target, Range("I5:I19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
tRow = Target.Row
Target = "=IF('PR'!G" & tRow & ">0,'PR'!G" & tRow & "*'PR'!H" & tRow & ",0)"
End If

ElseIf Not Intersect(Target, Range("H5:H19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
tRow = Target.Row
Target = "=IF(AND('PR'!G" & tRow & ">0,'PR'!G" & tRow & ">0),'PR'!I" & tRow & "/'PR'!G" & tRow & ",0)"
End If

ElseIf Not Intersect(Target, Range("G5:G19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
tRow = Target.Row
Target = "=IF('PR'!I" & tRow & ">0,'PR'!I" & tRow & "/'PR'!H" & tRow & ",0)"
End If

End If

'Exit on error
endo:
Application.EnableEvents = True
End Sub

[/vba]

The challenges I have are:
1. Let's say the user puts a value in Column G, and then Column H. Column I is calculated automatically. Now if the user decides to change the value in Column I, I want the original formula to jump back into Column G so it automatically updates. Currently the only way to make this happen is to delete the value in Column G (which then kicks off the above VBA code which returns the formula into the cell).

2. the other issue I have is that Excel keeps warning me that I have circular references every time i open the file. I want that warning to disappear, especially as users won't know what that is all about!

Many thanks!
Sunil

[Bob Phillips]

If you don't want a circular references message, turn it off, Tools>Options>Calculation, and set the iteration checkbox.

[sunilmulay]

thank you XLD, that resolved my issue no 2. I still have to resolve issue no 1 - any ideas, anyone???
Thanks

[rbrhodes]

Hi sunilmulay,

Normally I would have expected you to contact me by PM, email or thru my website... Clicking my name or my sig would do it.

Using a post in one thread to direct someone to another thread (esp. without specifying what the other one was) is rather unusual to say the least!

Anyways I found my way here and here's what I think you want:

- Col G must have a formula if Col I has a value entered into it

- Col I must have a formula if Col G has a value entered into it

- Col H doesn't care <g>

So this code checks if the column(s) have changed as always but, If Col G changes and isn't blank then it checks Col I for a formula and puts one in if if finds a value in Col I. Reverse for Col I changing.

Delete the code you have and replace it with this:

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim tRow As Long
On Error GoTo endo

Application.EnableEvents = False

'//Moved this out of if code so only occurs once
tRow = Target.Row

If Not Intersect(Target, Range("I5:I19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
Target = "=IF(G" & tRow & ">0,G" & tRow & "*H" & tRow & ",0)"
'//Added check
'Not blank(number entered), check Col G for formula
ElseIf Left(Target.Offset(0, -2), 3) <> "=IF" Then
'Col G is number so replace formula
Target.Offset(0, -2) = "=IF(and(I" & tRow & ">0,H" & tRow & ">0),I" & tRow & "/H" & tRow & ",0)"
End If

ElseIf Not Intersect(Target, Range("H5:H19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
Target = "=IF(and(G" & tRow & ">0,I" & tRow & ">0),I" & tRow & "/G" & tRow & ",0)"
End If

ElseIf Not Intersect(Target, Range("G5:G19")) Is Nothing Then
'Check if blank (deleted)
If Target = "" Then
'Yes. Restore formula
Target = "=IF(and(I" & tRow & ">0,H" & tRow & ">0),I" & tRow & "/H" & tRow & ",0)"
'//Added check
'Not blank(number entered), check Col I for formula
ElseIf Left(Target.Offset(0, 2), 3) <> "=IF" Then
'Col I is number so replace formula
Target.Offset(0, 2) = "=IF(G" & tRow & ">0,G" & tRow & "*H" & tRow & ",0)"
End If

End If

'Exit on error
endo:
Application.EnableEvents = True
End Sub
[/VBA]

[sunilmulay]

well...you can tell I'm not much of a techno-wiz yet! :-)
I didn't realise I could click on your name and get more info - duh!
Thanks for the above. As usual it works just as I hoped...
I may email you shortly about something else I'm trying to do....
Thanks!
Sunil