My take on your situation is that you find the project number (which is
entered in a textbox on a u/f) in column A of sheet 1. Assuming it is found
(if not, perhaps the problem), then you change the contents of the cells
respectively 1, 2, 3, 4 and 5 columns to the right of this cell. The values
entered into these 5 cells are derived from 5 other textboxes on the u/f. You
do likewise for sheet 2 except you only change the contents of the cell
immediately to the right of the cell where the project number is found.

It is not clear to me why the code you provided won't work even if the other
textboxes that provide the replacement values are empty. I offer this
streamlined code in the hope that we might at least achieve simplification
and clarification. With some clarification, others are more likely to join
in. It at least prevents raising an error if the project number is not found.

***Not tested. Possibly botched. Run it on a copy of your project.

Private Sub SaveProjectButton_Click()
Dim c As Range
Dim v As String
v = tbProjectNumber.Value
With Sheet1.Columns(1)
Set c = .Find(v)
If Not c Is Nothing Then
c(1, 2).Replace c(1, 2).Value, tbAEName.Value
c(1, 3).Replace c(1, 3).Value, tbSiteOwnerName.Value
c(1, 4).Replace c(1, 4).Value, tbPGLead.Value
c(1, 5).Replace c(1, 5).Value, cbProjectType
c(1, 6).Replace c(1, 6).Value, cbProjectCategory
End If
End With
With Sheet2.Columns(1)
Set c = .Find(v)
If Not c Is Nothing Then
c(1, 2).Replace c(1, 2).Value, tbAEName.Value
End If
End With
End Sub

Greg