title: excel 2010 vba make cell active
body: <p>I'm attempting to write an Excel macro that will take a column of data and edit for formatting errors. Background:</p>
  
  <ol>
  <li>Spreadsheet gets sent out to company with three name columns - LName, FName, MI</li>
  <li>Company sends it back, usually with combined FName and MI or with full middle name</li>
  <li>The state throws a fit and rejects the entire list if a single name comes across incorrectly - e.g. MI is a full name, there is a space in FName, the MI is included in the FName, MI is a zero instead of a letter, etc.</li>
  </ol>
  
  <p>I don't want to manually check nearly two thousand names once a month. It's a pain. So I figured I'd write a macro that does the following: </p>
  
  <ol>
  <li>be able to loop</li>
  <li>pull the MI if it's in the FName column and paste it into the next column</li>
  <li>"trim" or delete the space and any following text in the FName column</li>
  </ol>
  
  <p>Eventually I want to add a few other things, but they seem simple once I get this figured out.</p>
  
  <p>The problem:</p>
  
  <p>The entire sub seems to run from one cell, never changes the active cell, and therefore doesn't actually accomplish anything. The IF statement seems to think there is a space in every FName column, which isn't true. I'm positive this is another of those "extra pair of eyes" things, but I'm feeling awfully stupid and I know my brain is a little muddled with post-surgical pain meds. I shouldn't even BE at work (ugh, shutting up now).</p>
  
  <p>Even though I try to select AND activate the cell it SHOULD be on, it stays in whatever cell I've manually selected through all iterations, never changes, just plops the last letter of text into the next cell over whether there's a space or not. So the problems in bullet format are:</p>
  
  <ol>
  <li>Not selecting/activating the right cell(s).</li>
  <li>If statement is returning a positive even when it shouldn't.</li>
  <li>If statement is therefore breaking the whole stupid thing.</li>
  </ol>
  
  <p>Anyhow. Here's the code, and while I can't share the spreadsheet for HIPAA reasons, these are safe assumptions to be made:</p>
  
  <p>Column F has last names, Column G SHOULD have first names but often includes first names, a space and a middle initial (e.g. BOB C instead of BOB) and finally Column H SHOULD have only middle initials but often has full middle names or a zero if the person does not have a middle name (e.g. CHARLES instead of C or just a 0). I will get around to changing zeros to "" and trimming full middle names to initials in this or another function later.</p>
  
  <pre><code>Sub ReduceToInitial()
  
  Dim strInit As String
  Dim strName As String
  Dim r As Excel.Range
  Dim rCell As Excel.Range
  Dim lr As Long
  Dim oSht As Worksheet
  Set oSht = Application.ActiveSheet
  lr = Cells(Rows.Count, "G").End(xlUp).Row
  Set r = oSht.Range("G2:G" &amp; lr)
  Range("G2").Select
  Range("G2").Activate
  On Error Resume Next
  For Each rCell In r
  Range(rCell).Select
  Range(rCell).Activate
      If rCell.Find(" ", rCell) &lt;&gt; 0 Then
          strInit = Right(rCell, 1)
          ActiveCell.Offset(0, 1).Select
          ActiveCell.Formula = strInit
          ActiveCell.Offset(0, -1).Select
          strName = rCell.Left(rCell, rCell.Find(" ", rCell) - 1)
          ActiveCell.Formula = strName
      End If
  Next rCell
  
  End Sub
  </code></pre>
  
  <p>Please let me know if I haven't explained myself very well and I will try to do better.</p>