title: VBA: How to find the department and replace it with another department?
body: <p>I am having difficulty find a solution to this problem. I am trying to find the string <code>department</code> and replace it with some another department. There are <strong>16-10 sheets</strong> in each workbook that needs formatting. One of the formats requires moving/copying the <code>first department string</code> (which reaches out to into <code>columns B,C</code> in some rows) and pasting it where the <code>last department</code> was but the first department in some sheet in <code>columnA</code> doesn't have a department above it but <code>member number</code>. The reason is for this it to make the department a title.</p>
  
  <p>The picture in the bottom should provide more information.<a href="https://i.stack.imgur.com/W9zoJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/W9zoJ.png" alt="enter image description here"></a></p>
  
  <p>This is what I have so far but doesn't comply. I would appreciate some help on this.
  Please ask questions and give suggestions. Thank you.</p>
  
  <pre><code>    Sub format()
  
         Dim ws As Worksheet
         Dim frstDept As Long
         Dim lastDept As Long
         Dim rng As Long
         Dim n As Long
         Dim nlast As Long
         Dim rw As Range
         Dim i As Integer
         Dim iVal As Integer
         iVal = Application.WorksheetFunction.CountIf(Range("A1:A20000"), "Department")
         Debug.Print iVal
  
          Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
          nlast = rw.Count
  
          For Each ws In Sheets
              On Error GoTo NextWS
              For i = iVal To 1 Step -1
                  With ws
                          frstDept = Range("A:A").Find(What:="Department", LookIn:=xlValues, LookAt:=xlPart).Row
                          lastDept = Range("A:A").Find(What:="Member Number", LookIn:=xlValues, LookAt:=xlPart).Row
                              If .frstDept.Value = .lastDept.Value Then
                              frstDept.Selection.Offset(0, 2).Cut Destination:=Range("lastDept" + 1).Resize(1)
                              End If
                          End If
                      End With
       NextWS:
                  Err.Clear
  
                  Next ws
  
         End Sub
  </code></pre>