By the way, you may want to change your code slightly--just to make it a little
more readable.

You can use constants like:

if Result = 7 then

but there are constants built into excel.

?vbcancel
2

?vbyes
6

?vbno
7

?vbok
1

I find "if Result = vbNo then" much easier to debug later.


Alan M wrote:

please can you help with this ?

I have the following code which has until lately worked fine. Now when I run
the code I get a VB crtical sign and the error number 400 appear in a message
box. Whe I step through the code in VBA using F8 it runs through OK but the
results do not appear in the worksheet.
The final section of code with the range.offsets etc. do not work. Only the
first and last values appear in the target sheet.The remainder remain blank .
I cannot figure out why.

CODE

Public Sub DealSave()

Dim Profit As Range
Dim Target As Long
Dim Answer1 As Integer
Dim Answer2 As Integer

Set Profit = Worksheets("Used").Range("I15")
Target = 800
If Profit.Value < 0 Then
Answer1 = MsgBox(" Attention this deal is losing money!",
vbOKCancel + vbCritical, "Deal Profit Monitor")
If Answer1 = 2 Then
Exit Sub
End If

ElseIf Profit.Value < Target Then
Answer2 = MsgBox("Deal profit is less than £" & Target,
vbOKCancel + vbInformation, " Deal Profit Monitor")
If Answer2 = 2 Then
Exit Sub
End If
End If

Result = MsgBox("Do you want to add this deal to the database?", vbYesNo,
"Add Deal")

If Result = 7 Then Exit Sub


With Worksheets("Used Deals")

Set rng = Cells(Rows.Count, 1).End(xlUp)(2)

End With

rng.Offset(0, 0).Value = Worksheets("Used Offer").Range("D2")
rng.Offset(0, 1).Value = Worksheets("Used Offer").Range("K2")
rng.Offset(0, 2).Value = Worksheets("Used Offer").Range("Customer")
rng.Offset(0, 3).Value = Worksheets("Used Offer").Range("MakeModel")
rng.Offset(0, 4).Value = Worksheets("Used Offer").Range("RegNo")
rng.Offset(0, 5).Value = Worksheets("Used Offer").Range("Cash_Price")
rng.Offset(0, 6).Value = Worksheets("Used
Offer").Range("Vehicle_Replacement_Insurance")
rng.Offset(0, 7).Value = Worksheets("Used Offer").Range("Road_Fund_Licence")
rng.Offset(0, 8).Value = Worksheets("Used Offer").Range("Supaguard")
rng.Offset(0, 9).Value = Worksheets("Used Offer").Range("Sub_Total")
rng.Offset(0, 10).Value = Worksheets("Used
Offer").Range("Outstanding_Finance")
rng.Offset(0, 11).Value = Worksheets("Used Offer").Range("PartEx")
rng.Offset(0, 12).Value = Worksheets("Used Offer").Range("Customer_Deposit")
rng.Offset(0, 13).Value = Worksheets("Used Offer").Range("Balance_To_Change")
rng.Offset(0, 14).Value = Worksheets("Used Offer").Range("Months24")
rng.Offset(0, 15).Value = Worksheets("Used Offer").Range("Months36")
rng.Offset(0, 16).Value = Worksheets("Used").Range("I15")

End Sub

--

Dave Peterson