help with cells.find

[steve-m]

Hi

is there anyway using cell.find i can find an exact match on the number 0?

e.g
i have a column with value 0 and 111.07 i only want to find 0 values

example

Cells.Find(What:=0, After:=Cells(17, 17), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

 

[VoG]

Try

Cells.Find(What:=0, After:=Cells(17, 17), LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

 

[Andrew Poulsom]

Try with LookAt:= xlWhole.

 

[steve-m]

thanks for the reply's, that's done the trick. i have another question if you could help?

using

After:=Cells(32, 18)

i need to look in column 18 for zero values but the row position 32 is variable is there a way to do that?

 

[VoG]

Maybe like this where iRow holds the row number

Cells.Find(What:=0, After:=Cells(iRow, 18), LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

 

[steve-m]

I'm relatively new to vba could you please explain irow works?

further info:
I'm trying to use the cells.find to find the first "0" in column 18 an insert three blank rows above the code below is working apart from data i import to excel is different every time the macro runs.

Cells.Find(What:=0, After:=Cells(32, 18), LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    
    'The following line of code moves down two cells from "CFP Data ID"
    Selection.Offset(-1, 0).Select
    Dim Rng, n As Long, k As Long

    'These two lines of code fetch the number of rows you want to insert into the sheet
    'Rng = InputBox("Enter number of rows required.")
    If Rng = 1 Then Exit Sub
    
    'This line inserts a new row
    Range(ActiveCell, ActiveCell.Offset(Val(Rng) + 3, 0)).EntireRow.Insert

 

[VoG]

What determines the starting row number?

 

[steve-m]

nothing - i'm trying to search within column 18 to find the starting row.

i may need to rethink this code and do a loop on the rows to find the first value of "0" in column 18

 

[VoG]

Maybe omit the After argument and limit the search to column 18

Columns(18).Find(What:=0, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate