PDA

View Full Version : How to identify non-whole number?



YellowLabPro
07-25-2007, 05:52 AM
This is related to my other open post For-Next Loop.
I think it's better to post a separate question to keep it less confusing-

In this line of code I would like to create the condition to find a non-whole number. I have it written w/ .Value <> "Whole Number", for psuedo code.


If Cells(i, "F") = Item Or Cells(i, "G") = Item And Cells(i, "M").Value <> _"Whole Number" Then
Cells(i, "M").NumberFormat = "# ?/?"


Thanks

Bob Phillips
07-25-2007, 05:58 AM
Didn't I show you this yesterday?



If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
End If


or



If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Cells(i, "M").Value \ 1 Then
Cells(i, "M").NumberFormat = "# ?/?"
End If

YellowLabPro
07-25-2007, 06:31 AM
The second example you gave me yesterday- As I was working through it, I could not get it setup properly to work. I will test this one again now that I have spent time on the procedure and have a little more experience with it.

The first example, I don't believe so- but incorporating it into what I have now works perfectly- Thanks!

Bob Phillips
07-25-2007, 07:10 AM
The second example you gave me yesterday- As I was working through it, I could not get it setup properly to work. I will test this one again now that I have spent time on the procedure and have a little more experience with it.

It uses the integer divide operator \ not the normal divide operator /.

YellowLabPro
07-25-2007, 09:32 AM
xld-
I have not gone back to test that code yet. I have all but a small detail worked out. In the following code, this processes the numerical values exactly as I would like. However when it crosses a non-numeric value it breaks.
How do we deal w/ this, either A) put an on error resume or B) is there another If condition we can institute to handle it?

Here is my working code-

For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
Cells(i, "M").Interior.Color = vbYellow
Exit For
End If


Breaking here:
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then

I could use your assistance either way- The one error resume I have been told by you previously is not ideal in such cases it may mask other problems. So to trap this (I believe is the proper term), would be to condition the on error resume (if this is possible)
On error resume if .....

YellowLabPro
07-25-2007, 10:22 AM
Here is my new code w/ on error resume.
This appears to work. There is one glitch-
Text get shaded w/ vbYellow.
I have tried several options and nothing successful yet.


For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
If Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").Interior.Color = vbYellow
Exit For
End If
End If

Tommy
07-25-2007, 10:41 AM
This should work.

For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
If Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item Then
'checks to see if the value is a number
If IsNumeric(Cells(i, "M").Value) Then
'check for a fraction
If Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
Cells(i, "M").Interior.Color = vbYellow 'You dont get an error here?
Exit For
End If
End If
End If
Next
Next

Bob Phillips
07-25-2007, 10:58 AM
Here is my new code w/ on error resume.
This appears to work. There is one glitch-
Text get shaded w/ vbYellow.
I have tried several options and nothing successful yet.


For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
If Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").Interior.Color = vbYellow
Exit For
End If
End If




For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
If IsNumeric(Cells(i, "M").Value) Then
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
If Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").Interior.Color = vbYellow
Exit For
End If
End If
End If