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

Just a guess...

Sometimes when you run something manually, you'll have everything set up to make
it easy to see how things work.

You activate the worksheet that's gonna change and toggle back and forth between
excel and the VBE.

This section of code:

With Worksheets("Used Deals")
Set rng = Cells(Rows.Count, 1).End(xlUp)(2)
End With

actually doesn't use the "used deals" worksheet. rng is on the activesheet.
And if "used deals" isn't the activesheet, it might look like stuff isn't
getting done.

With Worksheets("Used Deals")
Set rng = .Cells(.Rows.Count, 1).End(xlUp)(2)
End With

(Notice the dots in front of .cells. That means to use the preceding "with"
statement.)

======
I'm not sure why this would cause an error of 400, though. I looked at
"trappable errors" in VBAs help and got this:

Form already displayed; can't show modally (Error 400)

You can't use the Show method to display a visible form as modal. This error has
the following cause and solution:

You tried to use Show, with the style argument set to 1 – vbModal, on an already
visible form.
Use either the Unload statement or the Hide method on the form before trying to
show it as a modal form.

=====
I didn't see anything in the code you posted that would have caused this.


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

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