confused as to why macro is not working correctly

[Amanda Emily]

Hello,

I am now quite confused as to why this sub macro keeps on declaring some
cell values to be exceeding 30. Basically I was given a bunch of
spreadsheets that computes mileage (and a few other variables) and
another spreadsheet that reads them in and creates a data file to be
imported into another system.

The macro I have written (and works elsewhere, please ignore the linewrap)
==================================
Sub CheckMiles(sheetCurrent As Worksheet)
'checks mode for more than 30 miles in a stop.
Dim bCell As Range
Dim bMax As Integer
Dim bMessageBox As String
bMax = 30
For Each bCell In sheetCurrent.Range("O17:O46")
If bCell.Value > bMax Then
bMessageBox = MsgBox("More than " & bMax & " in a mileage
count on " & sheetCurrent.Parent.Name & ".")
End If
Next bCell
End Sub
================================

It works if for example a computed value of say, O20 is 10.45678, but it
puts out the messagebox incorrectly if O20 is 4.1234567890.

Any ideas?

Thanks!

Amanda

 

[Trevor Shuttleworth]

Amanda

it probably doesn't help if I say "it works for me"

I suggest that you change the MessageBox in the loop to:

For Each bCell In sheetCurrent.Range("O17:O46")
If bCell.Value > bMax Then
bMessageBox = MsgBox("More than " & bMax & _
" in a mileage count on " & sheetCurrent.Parent.Name & _
" " & bCell.Address & " = " & bCell.Value & ".")
End If
Next bCell

That will identify the cell(s) that causes the message(s) and the associated
values. I think your code works, it may be that you have a value in there
somewhere that it doesn't like.

An alphabetic character will be picked up as greater than 30, as will a
space. A space in the middle of what looks like a numeric value would cause
the message too ... although spaces at either or both ends don't.

Regards

Trevor