I have a userform that uses a button (cbChangeButton) to look up an existing
values (textbox: tbExistingProjectNumber)in multiple (9)worksheets and then
changes it to a new value (textbox: tbChangeProjectNumberTo) on those
worksheets. My problem is that if the tbEXistingProjectNumber begins with a
"P" and tbChangeProjectNumberTo begins with an "E" I need to have certain
information from worksheet 7 copied to the next empty row of worksheet 8. I
also need to make it so that if tbExistingProjectNumber begins with "B" and
tbChangeProjectNumberTo begins with "P" to delete the entire row in which
that Project number is found on worksheet 9. I have included all of my code
for this button but the parts I need help on are near the bottom (I think I
have the right idea but just might not have it in the right order or I am
missing a piece). Thank you in advance for any help you can provide.


Private Sub cbChangeButton_Click()
' Project Number not entered yet, brings up ErrorHandler
If Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing Then
ufErrorHandler.Show
If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue) Is Nothing
Then
Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "P") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "E") Then
' Activate Sheet8
Sheet8.Activate
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet8(Experience List)
Cells(NextRow, 1) = tbChangeProjectNumberTo.Value
Cells(NextRow, 2) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 3).Value
Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 8).Value
End If
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") And
CStr(Left(tbChangeProjectNumberTo.Value, 1) = "P") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNu mber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Val ue).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
End If
End If
End Sub