An odd discovery I just made (I think) about Replace is that it seems only to
work within a wks's used range. Tests just now using a new wb found my code
wouldn't run until blank cells referenced by the macro were within the two
wks's used ranges. News to me.

Remedy was to simply suitably place a character on each of the two db wks at
or beyond the range referenced by the macro's Replace function. This solved
the problem. Apparently doesn't have to be any of the cells referenced by
Replace. Just has to expand the used range. Albeit, this based on extremely
cursory testing. You need to confirm.

I suspect this is the actual cause of your problem, namely: If the textboxes
on the first u/f are blank then they don't enter any data into the db wks's
and thus fail to expand their used ranges. Then when the second u/f attempts
the Replace method on blank cells beyond the used range it fails to work and
perhaps raises an error or causes a complication resulting in your situation.
Not sure if that is logical since I'm missing necessary info but advise you
check it out.

Greg



"Greg Wilson" wrote:

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