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.Value) Is Nothing Then
ufErrorHandler.Show
   If Not Sheet1.Columns(1).Find(tbExistingProjectNumber.Value) Is Nothing
Then
   Sheet1.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet1.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet1.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet2.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet2.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet2.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet3.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet3.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet3.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet4.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet4.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet4.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet5.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet5.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet5.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet6.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet6.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet6.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   Sheet7.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0, 0).Value
= Replace(Sheet7.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet7.Columns(1).Find(tbExistingProjectNumber.Value).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.Value).Offset(0, 3).Value
   Cells(NextRow, 3) =
Sheet7.Columns(1).Find(tbExistingProjectNumber.Value).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.Value).Offset(0, 0).Value
= Replace(Sheet9.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, Sheet9.Columns(1).Find(tbExistingProjectNumber.Value).Offset(0,
0).Value, tbChangeProjectNumberTo.Value)
   End If
   End If
End Sub