Find & replace issue

[Scoti]

I'm pretty new at this and have received a lot of help through this forum but I need just a little more help with an issue I can't seem to figure out. I am running this code and it works fine other than in addition to changing the cell in column B of the found row, it also changes the value in cell B1. In that cell I have a name of "Department" which should not be changed. The only value that should be changed is in the cell 2 columns to the left of the found cell value. Can anyone tell me what I have wrong here?

Here is my code:

Private Sub CommandButton1_Click()

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Dim FindString As String

Dim rng As Range

Dim d1 As Double

Dim d2 As Double

Set Sheet = Sheets("Manual_Dashboard")

d1 = Range("N12").Value

d2 = Range("N14").Value

For Each Cell In Range("D4:D43")

FindString = Cell.Value

With Sheets("Man_Received").Range("D:D")

Set rng = .Find(What:=FindString, _

After:=.Cells(.Cells.Count), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlDown, _

MatchCase:=False

If Not rng Is Nothing Then

Application.Goto rng, True

If CheckBox1.Value = False Then

rng.Offset(0, -2).Value = "Manual Digestion-Acid Consumption"

Range("N12") = d1 + Range("D3").Value

Else

rng.Offset(0, -2).Value = "Manual Digestion-Acid Consumption-Retest"

Range("N14") = d2 + Range("D3").Value

End If

End If

End With

Next

With Sheets("Manual_Dashboard")

Range("D4:D43").ClearContents

End With

Sheet.Activate

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True

End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find & replace Code - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.