Search not working now?

[BarefootPaul]

I wrote this code to search for a value in a column and then update the corresponding columns in that row, based on data entered into a userform. I swear it worked last week and now it isn't. Here is the code:

Private Sub cmdUpdate_Click()
    ActiveWorkbook.Sheets("Clients").Activate
    Range("C1").Select
    Do
    If ActiveCell.Value <> txtMaineCare.Value Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until ActiveCell.Value = txtMaineCare.Value Or ActiveCell.Value = ""
        If ActiveCell.Value = "" Then
            MsgBox ("There is no referral for this client.")
        Else
            ActiveCell.Offset(0, 9) = txtDOA.Value
            ActiveCell.Offset(0, 10) = cboResProgram.Value
            ActiveCell.Offset(0, 11) = cboPlacingAgency.Value
            If cboPlacingAgency.Value = "Other (specify)" Then
                txtPlaceOther.Enabled = True
                txtPlaceOther.SetFocus
                ActiveCell.Offset(0, 12) = txtPlaceOther.Value
            End If
            ActiveCell.Offset(0, 13) = txtGAFAdmit.Value
            ActiveCell.Offset(0, 14) = txtDischargePlan.Value
        End If
    Range("C1").Select
 
End Sub

It is suppossed to search column C until it finds the match, but even though I can can see that it is a match, it keeps telling me there is no match and the MsgBox to enter a referral comes up. Any suggestions would be helpful.

Thanks,
BarefootPaul

 

[jbeaucaire]

No need to physically activate sheets and select cells to find a value nor update the found row:

Private Sub cmdUpdate_Click()
Dim vFIND As Range

On Error Resume Next
With ActiveWorkbook.Sheets("Clients")
    Set vFIND = .Range("C:C").Find(txtMaineCare.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If vFIND Is Nothing Then
        MsgBox ("There is no referral for this client.")
    Else
        vFIND.Offset(0, 9) = txtDOA.Value
        vFIND.Offset(0, 10) = cboResProgram.Value
        vFIND.Offset(0, 11) = cboPlacingAgency.Value
        If cboPlacingAgency.Value = "Other (specify)" Then
            txtPlaceOther.Enabled = True
            txtPlaceOther.SetFocus
            vFIND.Offset(0, 12) = txtPlaceOther.Value
        End If
        vFIND.Offset(0, 13) = txtGAFAdmit.Value
        vFIND.Offset(0, 14) = txtDischargePlan.Value
    End If
End With
    
End Sub

The most common thing that makes strings not match that appear they should are hidden spaces in the data column or in the search string.

 

[BarefootPaul]

Awesome, thanks, that works beautifully!