Userform search for matching value not working

[BarefootPaul]

I posted this on the MrExcel forum and haven't gotten any responses. I'll try to frame the problem better here. I have a set of userforms that enters information about a client three different times with the second two searching the worksheet ("Clients") for a matching value in column C. If the value of a Text Box matches a value in that column then it updates cells in the corresponding row. It worked last week and I don't know what changed or why it isn't working. 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. Please return to main screen and enter a referral before returning to update client's admission status.")

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

'


I am hoping someone can point out why the code isn't working or what I might be missing. What happens right now is that it always gives me the MsgBox, even when I copy the value from a cell in column C into the txtMaineCare txtbox.


Thanks for you time,

BarefootPaul

 

[BarefootPaul]

I got a solution:


'

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. Please return to main screen and enter a referral before returning to update client's admission status.")

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

'