I am in a blank sheet in a worksheet.

Is there a way to return all the cells within the worksheet that have a
value behind the decimal? For example: 1,245.00 would not be returned
because it is 0 after the decimal but 1,245.01 would be returned. I am only
looking for this to 2 decimal places.

Thank you,

Steven

What do you want to do with the cells? Do you want to put them into an
Array, a Collection, highlight the cells, list the cell addresses in another
worksheet?
--
Cheers,
Ryan


"Steven" wrote:

I am in a blank sheet in a worksheet.

Is there a way to return all the cells within the worksheet that have a
value behind the decimal? For example: 1,245.00 would not be returned
because it is 0 after the decimal but 1,245.01 would be returned. I am only
looking for this to 2 decimal places.

Thank you,

Steven

Try this:

Sub findum()
Dim r As Range
Set r = Nothing
For Each cel In ActiveSheet.UsedRange
v = cel.Value
If IsNumeric(v) And v < "" Then
If v - Int(v) < 0 Then
If r Is Nothing Then
Set r = cel
Else
Set r = Union(r, cel)
End If
End If
End If
Next
MsgBox r.Address
r.Select
End Sub
--
Gary''s Student - gsnu201001


"Steven" wrote:

I am in a blank sheet in a worksheet.

Is there a way to return all the cells within the worksheet that have a
value behind the decimal? For example: 1,245.00 would not be returned
because it is 0 after the decimal but 1,245.01 would be returned. I am only
looking for this to 2 decimal places.

Thank you,

Steven

In this example, I built a loop to scan each cell in the used range in Sheet1
for values with decimals points. If the loop finds a decimal that doesn't
equal ".00" then it adds that cell to the collection. It will be up to you
to add code to do stuff to the collection. Let me know if you want help on
what you want to do with the collected cells. Hope this helps! If so, let
me know click "YES" below.

Sub FindDecimals()

Dim colMyCells As Collection
Dim cell As Range

Set colMyCells = New Collection

For Each cell In Sheets("Sheet1").UsedRange
If Right(cell.Text, 2) < "00" Then colMyCells.Add cell
Next cell

End Sub
--
Cheers,
Ryan


"Steven" wrote:

I am in a blank sheet in a worksheet.

Is there a way to return all the cells within the worksheet that have a
value behind the decimal? For example: 1,245.00 would not be returned
because it is 0 after the decimal but 1,245.01 would be returned. I am only
looking for this to 2 decimal places.

Thank you,

Steven

I'm assuming your numbers are formatted to two decimal places because you
showed us the thousands separator in your example values (so that leads me
to believe your values are formatted... your last sentence leads me to
believe that as long as they are formatted, you have included the two
decimal places along with the thousands separator in that format). If the
only values on the (active) worksheet are numbers or blank cells, then you
can use this macro...

Sub FindDecimalNumbers()
Dim Cell As Range, U As Range
For Each Cell In ActiveSheet.UsedRange
If Cell.Text Like "*#.##" And (Not Cell.Text Like "*.00") Then
If U Is Nothing Then
Set U = Cell
Else
Set U = Union(U, Cell)
End If
End If
Next
U.Select
End Sub

If, on the other hand, you can have text values on the worksheet along with
your numbers AND some of those text values can end in a digit followed by a
dot followed by two more digits, then use this macro instead...

Sub FindDecimalNumbers()
Dim Cell As Range, U As Range, SC As Range
On Error Resume Next
Set SC = ActiveSheet.UsedRange.SpecialCells( _
xlCellTypeConstants, xlNumbers)
If SC Is Nothing Then Exit Sub
On Error GoTo 0
For Each Cell In SC
If Cell.Text Like "*.*" And (Not Cell.Text Like "*.00") Then
If U Is Nothing Then
Set U = Cell
Else
Set U = Union(U, Cell)
End If
End If
Next
U.Select
End Sub

--
Rick (MVP - Excel)


"Steven" wrote in message
...
I am in a blank sheet in a worksheet.

Is there a way to return all the cells within the worksheet that have a
value behind the decimal? For example: 1,245.00 would not be returned
because it is 0 after the decimal but 1,245.01 would be returned. I am
only
looking for this to 2 decimal places.

Thank you,

Steven