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.

 

[mysticmario]

So I took upon your advice and move the execution of the code onto a different sheet with no merged cells. I tried finding value in range this time around and failed with mismatch error once again.

Sub weryfikacja_materialow()
Dim cell As Variant
Dim rng As Range
Set rng = ActiveSheet.Range("B9:T30")

Set cell = rng.Find(what:="Legs", After:=Cells(1, 4), LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell <> "" And cell.Offset(, 1) <> 0 Then
    MsgBox "Woudl you like to add adaptor to  furniture legs?", vbYesNo
    If vbYes Then
    ActiveSheet.Cells(lr, "B").Value = "Adaptor for furniture legs"
    ActiveSheet.Cells(lr, "C").Value = cell.Offset(, 1) & "pcs"
    lr = lr + 1
    End If
End If
    Application.ScreenUpdating = 1
    MsgBox "Done"

End Sub

 

[Fluff]

Try

Set cell = rng.Find(what:="Legs", LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

 

[Joe4]

It appears that you are trying to use a variable named "lr" that you have not defined or set to anything (at least not until you already tried to use it once).

 

[mysticmario]

It appears that you are trying to use a variable named "lr" that you have not defined or set to anything (at least not until you already tried to use it once).

Ah ye s that's a typical copy paste mistake, thank you will declare it before I get another error

 

[Joe4]

Ah ye s that's a typical copy paste mistake, thank you will declare it before I get another error

Not only declare, but you need to set it equal to something before you use it the first time!
Otherwise, its value will be 0, and there is no row 0 on worksheets (which will lead to errors!).

 

[mysticmario]

Not only declare, but you need to set it equal to something before you use it the first time!
Otherwise, its value will be 0, and there is no row 0 on worksheets (which will lead to errors!).

yes I know. This is the spot where I need to implement a huge bit of code that someone else wrote me few months back because it was out my depths.
We'll see how it goes

 

[mysticmario]

Thank you guys you've been a great help. just to satisfy your curiosity if you had any towards this here's the final code:

Sub weryfikacja_materialow()
Dim cell As Variant
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lr As Long, i&, j&, S, T%, X
S = Array("B", "K", "T")
Set rng = ActiveSheet.Range("B9:T30")

Set cell = rng.Find(what:="Nóżki", LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell <> "" And cell.Offset(, 1) <> 0 Then
    MsgBox "Would you like to add adaptor to  furniture legs?", vbYesNo
    If vbYes Then
    
    With ws
T = Application.WorksheetFunction.CountA(.Range("B11:B30,K11:K30,T11:T30"))
        If T >= 60 Then MsgBox "Full line.Please check data": Exit Sub
            If T > 0 Then
            X = Int(T / 20)
            lr = .Cells(30, S(X)).End(3).Row
            Else
            lr = 10
            X = 0
            End If
        End With
        lr = lr + 1
    If lr < 31 Then
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Adaptor for furniture legs"
        ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Offset(, 1)
    Else
            MsgBox "Check the return area": Exit Sub
        End If
    Else
        X = X + 1
        lr = lr - 20
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Adaptor for furniture legs"
        ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Offset(, 1)
                Else
                    MsgBox "Check the return area": Exit Sub
                End If
            End If
        End If
     End If
     
    Application.ScreenUpdating = 1
    MsgBox "Done"

End Sub