Cannot Activate Cell

[nniedzielski]

I am running the code below, the cell that should be activating is A2, somehow R1 keeps getting activated and the next steps are happening in the wrong spot.

what am i doing wrong here?

Private Sub cmdOk_Click()    
    If chkdefective.Value = True And optPlastic.Value = True Then
    With Worksheets("SealLog").Range("A:A")
            Worksheets("SealLog").Activate
            Cells.Find(txtSealnumberPlastic, LookIn:=xlValues).Activate
                ActiveCell.Offset(0, 5) = "Defective"
                ActiveCell.Offset(0, 6) = "y"
    End With
    End If
End Sub

 

[Gary McMaster]

You don't have to activate worksheets or cells to access their data. In fact doing so just slows things down.

Try the following without the "activates". It should work even if "SealLog" is not the active sheet.

Private Sub cmdOk_Click()

    Dim oFound As Range
    
    If chkdefective.Value = True And optPlastic.Value = True Then
        Set oFound = Worksheets("SealLog").Range("A:A").Find(txtSealnumberPlastic, LookIn:=xlValues)
        If Not oFound Is Nothing Then
            oFound.Offset(0, 5) = "Defective"
            oFound.Offset(0, 6) = "y"
        End If
    End If
    
End Sub

 

[MARK858]

Adding to what Gary has stated

Cells.Find(txtSealnumberPlastic, LookIn:=xlValues).Activate

Cells is looking at the entire worksheet not a single column as looks like your intention.
You are not using any of the parameters of the Find function and so you are taking a chance that they are set up to look in the right direction and from the right starting point.

With Worksheets("SealLog").Range("A:A")

You are using a With statement but then not committing any action to it and so this line isn't doing anything.

Possibly the below is something like you want.

Private Sub cmdOk_Click()
    Dim x As Range
    If chkdefective.Value = True And optPlastic.Value = True Then
        With Worksheets("SealLog").Range("A:A")
            Set x = .Find(What:=txtSealnumberPlastic, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt _
                                                                                                      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
            x.Offset(0, 5) = "Defective"
            x.Offset(0, 6) = "y"
        End With
    End If
End Sub