First, using application.volatile isn't really a fix. Read those other posts
about how calculation works.

Second, I'd try to get the formula working manually before I tried to make it
work in code. Try that with one of the workbooks that caused the trouble.

I don't think I can offer any more suggestions. Setting up test workbooks
wouldn't be worth it. If there's a mistyped name, I'd never see it.

Brettjg wrote:

Hi Dave

I just posted this reply to you but something very odd happened, so I'm
going to post it again (and copy to clipboard this time)

The Public functions seems to have been resolved by including
Application.Volatile, but there are two other areas of concern. The second is
the more important and I'll come to that.

In this workbook that I'm fooling around with there are various little
groups of cells that get data from another book via vlookup. Any cell that
has an actual value to return is fine but any that returnd blank (nothing in
the other workbook) got to #REF every now and then. Nothing has changed in
either group of cells *this book or the other) and if I update links
everything is back to normal again until maybe another macro runs. This never
used to happen, and I know I could run update links in the macro that I think
MAY be causing it (if it's a macro at all) but that seems inelegant and
unecessary. Do you have any thoughts on this please?

The main problem that I'm having is with the following change procedu
(I'll add some notes at the bottm)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Integer, cl As Integer, rw_L As Integer, rw_Sec As Integer,
spt_abs As Variant, loan_total As Long, answer As Variant
If Target.Count 1 Then Exit Sub
If Not Sheets("LOANS").Range("QUO.RUNNING") = "RUNNING" Then: Exit Sub
rw = Target.Row
cl = Target.Column

Select Case rw 'LOAN PRODUCT CHANGE
Case 14, 53, 92, 131, 170
Select Case cl
Case 4, 7, 10, 13, 16: GoTo CHANGE_LOAN_PRODUCT ':
Application.EnableEvents = False
Case Else: Exit Sub
End Select
End Select

Select Case rw 'LOAN CHANGE
Case 39, 78, 117, 156, 195
Select Case cl
Case 4, 7, 10, 13, 16: Application.EnableEvents = False:
GoTo LOAN_CHANGE
Case Else: Exit Sub
End Select
End Select

Select Case rw 'R or U CHANGE
Case 13, 52, 91, 130, 169
Select Case cl
Case 3, 6, 9, 12, 15
If (Cells(rw + 25, 17).Value + Cells(rw - 4, 7)) /
Cells(rw - 8, 7) 0.8 Then: Application.EnableEvents = False: GoTo RU_CHANGE
Case Else: Exit Sub
End Select
End Select
Exit Sub

CHANGE_LOAN_PRODUCT:
'countRX = Range("RX.count").Value
'countYX = Range("YX.count").Value

rw = Target.Row
cl = Target.Column
MsgBox "FROM CHANGE_LOAN 1"
MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
Target.Value = 100
Target.Offset(-18, 0).FormulaR1C1 =
"=IF(R[18]C)10000,PERSONAL.xls!REFI_PROD_CODE(R[18]C),)"
MsgBox "FROM CHANGE_LOAN 2"
Target.Value = Target.Offset(-18, 0).Value
'spt_abs = Cells(rw + 1, cl + 1).Value
'MsgBox "A = " & (rw + 25) / 39 & " Target.Value = " & Target.Value & "
SPLIT = " & (cl - 1) / 3
'Application.Run "PERSONAL.xls!RESET_REFINANCE", (rw + 25) / 39,
Target.Value, (cl - 1) / 3
MsgBox "RETURN from RESET_REFINANCE"

GoTo EXIT_SUB 'NOTHING ELSE QUALIFIES

LOAN_CHANGE:
Application.ScreenUpdating = False
Cells(rw, cl + 1).Value = Cells(rw, cl).Value
rw_L = rw: rw_Sec = rw - 34
Cells(rw_L - 1, 17).Value = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value +
Cells(rw_L, 11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
GoTo LVR_CHECK

RU_CHANGE:
Application.ScreenUpdating = False
rw_L = rw + 26: rw_Sec = rw - 8: cl = cl + 1

LVR_CHECK:
loan_total = Cells(rw_L, 5).Value + Cells(rw_L, 8).Value + Cells(rw_L,
11).Value + Cells(rw_L, 14).Value + Cells(rw_L, 17).Value
If (loan_total + Cells(rw_Sec + 4, 7)) / Cells(rw_Sec, 7) <= 0.8 Then:
Cells(rw_L - 3, 19).ClearContents: Cells(rw_L - 4, 19).ClearContents: GoTo
EXIT_SUB

GET_LMI:
Application.Run "PERSONAL.xls!WAV_DING"
answer = MsgBox("ARE YOU READY TO CALCULATE THE LMI PREMIUM YET?",
vbOKCancel + vbDefaultButton2 + vbQuestion, "CALCULATE LMI PREMIUM")
If answer = 2 Then: GoTo EXIT_SUB

Application.Calculation = xlCalculationManual
Cells(rw_L - 4, 19).ClearContents: Cells(rw_L - 3, 19).ClearContents
Application.Run "PERSONAL.xls!GET_LMI_APP", rw_L, cl

EXIT_SUB:
If Application.Calculation = xlCalculationManual Then:
Application.Calculation = xlCalculationAutomatic
If Application.EnableEvents = False Then: Application.EnableEvents = True
If Application.ScreenUpdating = False Then: Application.ScreenUpdating = True
End Sub

There are 3 main areas of execution"
one for LOAN_CHANGE and it works perfectly
one for RU_CHANGE and it works perfectly
one for CHANGE_LOAN_PRODUCT and it is a very difficult child indeed.

I use Select Case to narrow the field because the code is brief and very
specific.

I know that CHANGE_LOAN_PRODUCT startst becasue the first two Msgboxes come
up, but from the Target.value = 100 nothing happens. The most important line
to execute is 'Application.Run "PERSONAL.xls!RESET_REFINANCE" but I have
commented that out because it won't work from here (works perfectly from 5
click events however).

There is a selection change sub but the Select Case in there eleiminates any
confusion with these cells (commenting it and the click subs out makes no
difference). I've tried various other simple instructions where I have
Target.Value = 100 but nothing at all will go. I only put that particular
line in for debugging (100 is one of the values that can be returned by the
Public Function). After that I'm struggling. Regards, Brett

--

Dave Peterson