sunilmulay
11-13-2008, 02:15 AM
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.
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
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
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.
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
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