Message Box Formula

[mar050703]

Hello

I need some help please.

I am using the following code:

LastDeal = Sheets("Data").Range("A1").End(xlDown).Row - 1
Cost = Sheets("Data").Range("E1").End(xlDown).Row - 1
SaleValue = Sheets("Data").Range("Q1").End(xlDown).Row - 1
Profit = Val(SaleValue) - Val(Cost)

Sheets("Deal Sheet").Select
        Range("E2").Select
        ActiveCell.Value = LastDeal


'ask if want to print deal sheet
Response = MsgBox("Do you want to print the Deal Sheet?", vbYesNo, "Print")
    Select Case Response
        Case vbYes
        'Sheets("Deal Sheet").Select
        'Range("E2").Select
        'ActiveCell.Value = LastDeal
        ActiveWindow.SelectedSheets.PrintOut 1, 1, copies:=1
            Case vbNo
                MsgBox "You will need to print out the Deal Sheet for Deal...  " & LastDeal, vbExclamation, Warning
    End Select
'ask if an Invoice has been rasied
Response = MsgBox("Have you raised an Invoice?", vbYesNo, "Invoice")
    Select Case Response
' if yes then place the invoie number in the relevant cell
        Case vbYes
            Invoice = InputBox("What is the Invoice Number?", "Invoice")
            With Sheets("Data")
                With .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 0)
                     .Offset(0, 34).Value = Invoice
                End With
            End With
 ' if not yet rasied, then remind user to rasie the invoice, and quote the profit
        Case vbNo
            MsgBox "You need to raise an Invoice for Deal...  " & LastDeal & vbNewLine & "The Total Invoice Value is:... " & Profit, vbOKOnly
    End Select

However the final message box code does not work, I get "The Total Invoice Value is... 0", but in my test Profit is 1,000.

I have also tried using a named range within Excel (called "Profit"), which refers to "Deal Sheet" worksheet, and cell C32 (all in the same workbook)

I am sure this is a simple fix, but it appears to escape me at present.

Thanks

 

[SkipVought]

Hi,

I'd STEP thru you code and check the values that are in LastDeal, Cost & SaleValue or put Debug.Print statements in your code prior to assigning Profit.

 

[mar050703]

Skip

Thanks, I tried Debug.Print, but still I get the same problem.

I will keep trying, maybe in a sample program.

Any other ideas would be appreciated however.

Thanks

 

[SkipVought]

What were the results of your test?

Where were your Debug.Print statements in your code?

You can't just say, "I still have the same problem."

Did you step thru your entire code to see what statements get executed? This is what code debugging is all about.

 

[Andrzejek]

The only place where you assign a value to Profit is at the top of your code:[tt]
Profit = Val(SaleValue) - Val(Cost)[/tt]
So I would check there if you have any values in SaleValue and in Cost.

Aside the message box, why do you use them?
First you ask: “Do you want to print the Deal Sheet?" and if user answers No, he gets: “You will need to print out the Deal Sheet for Deal” So it is not really a Yes/No choice, is it?
Then you ask: “Have you raised an Invoice?" and if the answer is No - "You need to raise an Invoice for Deal... " Again, this is not a Yes/No deal


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thanks Guys

The debug.print did not bring up an error. Mainly as it just gave the wrong answer.

However I have found a solution I have gone back to referencing the cell within the sheet, using

 MsgBox "You need to raise an Invoice for Deal...  " & LastDeal & vbCrLf & _
            "The Total Invoice Value is:... " & Format([Gross], "£#,##0.00") & vbCrLf & _
            "The Net Invoice Value is:... " & Format([Net], "£#,##0.00"), vbOKOnly

Andy, in relation to yes/no, I guessyou are right. I have never used this type of MsgBox. I will have a look

Thanks again

 

[Andrzejek]

I have never seen the Debug.Print giving “the wrong answer”. The answer may not be what you would expect, but Debug.Print is always right.

To help you with the Message Box, you may ‘invest’ in MZTools There is a very nice Message Box Assistant which makes creating MsgBox very easy. It even writes the code for you.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

What was/wereyour debug statement(s) and what was/were the result(s)?

 

[SkipVought]

As I implied earlier, just making a vague allegation does not provide any sort of relevant information that can be used to diagnose what migh be the problem.

You need to provide yourself primarily and those who would attempt to supply a solution, adequate information to track down what is actually happening. Just trying different things is not a very good method.

Please supply the information requested if you wish to get some relevant help.

 

[mar050703]

Thanks for all your help.

What I meant was that Debug.Print did not throw any errors, as the coding was clearly ok, (according to VBA), but for what I expected it to do, and the answer I expected, this was the problem. I guess how I have SaleVale and Cost working.

However I have now resolved my problem using with the coding in my last post. I am no longer using SaleValue or Cost.

I did have the Debug.Print at the beginning of the original code, had there been an error, I trust that code would still have thrown the error for me.

Thanks again

 

[combo]

With your initial code you assign last row numbers to variables LastDeal,...,Profit. I guess that you intended to use values in those cells. In this case you need:
[tt]With Sheets("Data")
LastDeal = .cells(.Range("A1").End(xlDown).Row,1) - 1
End With[/tt]
Check if the code returns the row you need.


combo

 

[strongm]

>The debug.print did not bring up an error
> Debug.Print did not throw any errors

Debug.print has nothing to do with throwing (or capturing) an error. What everyone was hoping you would do is something like this near the top of your code

LastDeal = Sheets("Data").Range("A1").End(xlDown).Row - 1
Cost = Sheets("Data").Range("E1").End(xlDown).Row - 1
SaleValue = Sheets("Data").Range("Q1").End(xlDown).Row - 1
Profit = Val(SaleValue) - Val(Cost)

Debug.Print Val(SaleValue)
Debug.Print Val(Cost)

And check the results.

>had there been an error, I trust that code would still have thrown the error for me.
I'm not sure what sort of error you are expecting VB to pick up and report.