Format What Appears in Message Box

[kspence703]

I am working with the following code:

Dim i As Integer
Dim j As Integer
Dim nhigh As Integer
Dim cutoff As Currency

cutoff = InputBox("What Sales value do you want to look for?")

If cutoff = "" Then
Exit Sub
End If

For j = 1 To 6
nhigh = 0
For i = 1 To 36
If wsData.Range("Sales").Cells(i, j) >= cutoff Then _
nhigh = nhigh + 1

Next i
MsgBox "For region " & j & ", sales were above " & _
Format(cutoff, "$0,000") _
& " on " & nhigh & " of the 36 months."

Next j

First question is how do i set up the code so that if someone hits cancel on the input box the sub simply stops running. When I change the Dim cutoff As Currency portion of the code to Dim cutoff as String the code for clicking cancel and ending the sub works BUT then there are issues with the Message-box where it does not correctly report results. For instance in the Dim cuttoff a string when I check to see how many months sales were over say $45 the return will say something like 4 out of 36 months even though the data I am working with has sales of over $45 in EVERY SINGLE month...PLEASE HELP LOL

 

[Puertorekinsam]

just move your variables around a bit:

Dim cutoff As Currency
Dim cutoff1
cutoff1 = InputBox("What Sales value do you want to look for?")

If cutoff1 = "" Then
Exit Sub
Else:
cutoff = cutoff1
End If

 

[kspence703]

Thanks!

Any insight into the second part of that question?

 

[Puertorekinsam]

I would need to see your data. It could be an issue of the format the data is in (string vs. Number), what happens if you add a *1 to the formula check?

If wsData.Range("Sales").Cells(i, j)*1 >= cutoff Then _