title: Search, Copy, Insert row, Paste and Change Value
body: <p>I am trying to find a value "PLGDY", copy data from this row, insert a new row above the one found, paste the data into new row, replace value "PLGDY" with "PLGDN".</p>
  
  <p>I wrote a macro that instead of copying data into new row it pastes into cells to the right. It also changes values in both rows to "PLGDN".</p>
  
  <p>I would like to use For Next loop, because I have plenty of values to change. Is it possible to check how many values to change? I would like to use this number as counter.</p>
  
  <pre><code> Sub Find_and_Change()
  '
  
  'Find a "PLGDY" and set an active cell
  Cells.Find(What:="PLGDY", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
  
  'select a block of data in a row
          Range(Selection, Selection.End(xlToRight)).Select
          Range(Selection, Selection.End(xlToLeft)).Select
          Range(Selection, Selection.End(xlToLeft)).Select
          Range(Selection, Selection.End(xlToLeft)).Select
  'copy selected block of data
          Selection.Copy
  'insert a row above  active cell
          ActiveSheet.Cells(ActiveCell.Row, 1).Select
          ActiveCell.EntireRow.Insert
  'set an active cell at the beginig of a row and move into column A 
          ActiveSheet.Cells(ActiveCell.Row, 1).Select
  ' paste copied data into this cell
          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  'Now I select whole row
          ActiveCell.EntireRow.Select
  'I need to replace PLGDY with PLGDN in this row
  Selection.Replace What:="PLGDY", Replacement:="PLGDN", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
  'I need to move active cell 10 columns right and one row down because I want to find next PLGDY
          ActiveCell.Offset(1, 10).Select
  
  End Sub
  </code></pre>