Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro not working correctly | Excel Discussion (Misc queries) | |||
Formula not working and I am extremely confused | Excel Worksheet Functions | |||
Formulas not working correctly | Excel Worksheet Functions | |||
SUM formula not working correctly | Excel Worksheet Functions | |||
VBE Window not working correctly | Excel Programming |