Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find do not work with decimal point
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find do not work with decimal point
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find do not work with decimal point
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find do not work with decimal point
It work after changing to double.
Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Point... | Excel Discussion (Misc queries) | |||
Decimal point | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
decimal point override does not work | Excel Discussion (Misc queries) |