confused as to why macro is not working correctly
 

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

confused as to why macro is not working correctly
 

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


"Amanda Emily" wrote in message
...
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