find selects almost entire worksheet instead of just column D

[mysticmario]

as per title.
I have similar code in different part of my workbook and it works fine, but as soon as i started playign with offsets I have this problem.

    Sub TEST()
    ActiveSheet.Columns("D:D").Select
Set cell = Selection.Find(what:="legs", After:=ActiveCell, LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Not IsEmpty(cell.Offset(, 2)) And cell.Offset(, 2) <> 0 Then
    MsgBox "no legs"
Else
    MsgBox "yes legs"
End If

    End Sub

I assume i need some more stuff to make it work.

 

[Fluff]

I get a type mismatch error

On which line?

 

[mysticmario]

just after i try to execute this:

If cell.Offset(, 2) <> "" And cell.Offset(, 2) <> 0 Then

 

[Fluff]

That surprises me, as judging by your image that will be looking at col F which will be empty as it's merged with col E.
Best thing you can do is get rid off all the merged cells, they are an abomination & will give you nothing but trouble.

 

[Joe4]

Note that you can often use the "Center Across Selection" formatting option instead of merging cells across rows.
This gives you the same visual effect without all the issues that merged cells cause.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[mysticmario]

I got it.
It was all about the text it was trying to find it didnt match to anything. However if that was thew case I assume it shodyul still run the chekc and just throw msgbox no legs.

 

[Fluff]

This If cell.Offset(, 2) <> "" And cell.Offset(, 2) <> 0 Then will never be true & so should just take you to the Else.
If you are getting an error on that line, you will have to un-merge the cells to find out why.

 

[mysticmario]

changed If cell.Offset(, 2) <> "" And cell.Offset(, 2) <> 0 Then to this: If cell.Offset(, 4) <> "" And cell.Offset(, 4) <> 0 Then
so it now actually goes for collumn H

 

[Fluff]

And does it work?

 

[Alex Blakenburg]

It was all about the text it was trying to find it didnt match to anything. However if that was thew case I assume it shodyul still run the chekc and just throw msgbox no legs.

Try adding the cell is nothing test:

   Sub TEST()
    Dim cell As Range
    ActiveSheet.Columns("D:D").Select
    Set cell = Selection.Find(what:="legs", After:=ActiveCell, LookIn:=xlFormulas, _
            lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
    If cell Is Nothing Then
        MsgBox "no legs"
        Exit Sub
    End If

    If Not IsEmpty(cell.Offset(, 2)) And cell.Offset(, 2) <> 0 Then
        MsgBox "no legs"
    Else
        MsgBox "yes legs"
    End If

    End Sub

 

[mysticmario]

And does it work?

It did work, but then I had to adapt it to my file and "**** hit the fan".