Cannot Activate Cell

nniedzielski

Active Member
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?

Code:
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

Well-known Member
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.

Code:
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

Well-known Member
Adding to what Gary has stated

Code:
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.

Code:
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.

Rich (BB code):
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
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top