Find do not work with decimal point

[[email protected]]

I have 2 list of data, and try to use find method to match the data.
However, i noted that find method do not work if the value to find is
with decimal point.
eg if the value is 12345, the target range is marked, but if the value
is 12345.5, then find miss the cell even though it really exist.

Do you have any idea?

-----------------------
Dim y As Integer, x As Integer
Dim i As Integer, j As Integer
Dim B As String, A As String, A1 As Long, B1 As Long


x = WorksheetFunction.CountA(Columns(1))
i = WorksheetFunction.CountA(Columns(6))

For y = 1 To x

If Cells(y, 2) = "ABCDEF" Then
A1 = Cells(y, 4) * -1

On Error Resume Next
Err.Clear

ActiveSheet.Range("J3:J" & i).Find(what:=A1,
lookat:=xlWhole).Select

If Not Err <> 0 Then
B = ActiveCell.Offset(0, -1).Value

If Left(B, 4) = "XFR " Then

ActiveCell.Offset(0, 1).Value = "y"
Cells(y, 5).Value = "y"

End If
End If
End If

Next

[Toppers]

Check that you are looking through all valid cells as COUNTA gives count of
non-blank cells; check values of i and x. If there are any blank cells in
your data range, your logic will not work.

And "Find" will find decimal data.


HTH

"[email protected]" wrote:

> I have 2 list of data, and try to use find method to match the data.
> However, i noted that find method do not work if the value to find is
> with decimal point.
> eg if the value is 12345, the target range is marked, but if the value
> is 12345.5, then find miss the cell even though it really exist.
>
> Do you have any idea?
>
> -----------------------
> Dim y As Integer, x As Integer
> Dim i As Integer, j As Integer
> Dim B As String, A As String, A1 As Long, B1 As Long
>
>
> x = WorksheetFunction.CountA(Columns(1))
> i = WorksheetFunction.CountA(Columns(6))
>
> For y = 1 To x
>
> If Cells(y, 2) = "ABCDEF" Then
> A1 = Cells(y, 4) * -1
>
> On Error Resume Next
> Err.Clear
>
> ActiveSheet.Range("J3:J" & i).Find(what:=A1,
> lookat:=xlWhole).Select
>
> If Not Err <> 0 Then
> B = ActiveCell.Offset(0, -1).Value
>
> If Left(B, 4) = "XFR " Then
>
> ActiveCell.Offset(0, 1).Value = "y"
> Cells(y, 5).Value = "y"
>
> End If
> End If
> End If
>
> Next
>
>

[Jan Karel Pieterse]

> I have 2 list of data, and try to use find method to match the data.
> However, i noted that find method do not work if the value to find is
> with decimal point.
> eg if the value is 12345, the target range is marked, but if the value
> is 12345.5, then find miss the cell even though it really exist.
>

Yes:

change

lookat:=xlWhole

to:

lookat:=xlPart

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

[keepITcool]

you store the find value in a LONG variable..
and then use the LONG to find a match...
and then you complain that it doesn't find decimal values...

hmm....

maybe changing a1 to Double will help !

<g>


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


[email protected] wrote :

> I have 2 list of data, and try to use find method to match the data.
> However, i noted that find method do not work if the value to find is
> with decimal point.
> eg if the value is 12345, the target range is marked, but if the value
> is 12345.5, then find miss the cell even though it really exist.
>
> Do you have any idea?
>
> -----------------------
> Dim y As Integer, x As Integer
> Dim i As Integer, j As Integer
> Dim B As String, A As String, A1 As Long, B1 As Long
>
>
> x = WorksheetFunction.CountA(Columns(1))
> i = WorksheetFunction.CountA(Columns(6))
>
> For y = 1 To x
>
> If Cells(y, 2) = "ABCDEF" Then
> A1 = Cells(y, 4) * -1
>
> On Error Resume Next
> Err.Clear
>
> ActiveSheet.Range("J3:J" & i).Find(what:=A1,
> lookat:=xlWhole).Select
>
> If Not Err <> 0 Then
> B = ActiveCell.Offset(0, -1).Value
>
> If Left(B, 4) = "XFR " Then
>
> ActiveCell.Offset(0, 1).Value = "y"
> Cells(y, 5).Value = "y"
>
> End If
> End If
> End If
>
> Next

[[email protected]]

It work after changing to double.
Thanks