title: Problems with ActiveCell.End.next
body: <p>I'm writing a comparator function in VB for my column re-organization program. The program will take a sheet, re-organize it's columns based on a reference sheet. The issue I'm currently trying to fix is this: Hypothetically, what if the column (in the reference sheet) doesn't exist in the sheet to be re-organized? I can't just delete an entire column, if I want all of my sheets to be uniform. So what I'm planning to do is to create the column WITH the name given.</p>
  
  <pre><code>Alternative = InputBox(Sheet2Header &amp; _
  " not found. Please enter the name of the column you would like to substitute " _
  &amp; "Or Press Cancel to Create a new Column to fill in the gap.")
  Do
      If Alternative = "" Then
          Set comparator = ActiveCell.End(xlToRight).Next
          comparator.Activate
          ActiveCell.Value = Sheet2Header
          Exit Function
      End If
      With Rows(1)
          Set found = .Find(what:=Alternative, After:=Cells(1, 1), lookat:=xlWhole)
      End With
      If found Is Nothing Then
          Alternative = InputBox("Column Name incorrect, Try again")
      Else: Exit Do
      End If
  Loop
  </code></pre>
  
  <p>This is my Do loop. If the column name doesn't exist within the sheet to be sorted, it asks for you to write in your own column name and see if THAT column name exists within the sheet (for example Employee ID might be listed as PersonID, so it accounts for that)</p>
  
  <p>If you press cancel however, It's supposed to go to the very last column.next(First empty space) and set the return range equal to that cell, plug in the value, and then exit the function.</p>
  
  <p>The problem I'm getting is in:
      Set comparator = ActiveCell.End(xlToRight).Next
  It works fine with the first column that doesn't exist, and it works fine with the second column that doesn't inherently exist, but once it gets to the third it gives me:</p>
  
  <pre><code>Run-time error '1004'
  Microsoft Excel cannot find the data you're searching for.
  </code></pre>
  
  <p>(or the droids for that matter)</p>
  
  <pre><code>If you are certain the data exists in the current sheet, check what 
  you typed and try again.
  </code></pre>
  
  <p>I'm sorry for the wall of text, but I wanted to give you as much detail as would be necessary to help me solve this. Anyone have any ideas?</p>
  
  <p>EDIT: I SOLVED THE PROBLEM</p>
  
  <p>No need to solve this anymore, I figured it out. I'm not deleting this in case anyone else has a similar problem and can learn from my efforts. If your activeCell IS ActiveCell.End(xlToRight) then you can't declare ActiveCell.End(xlToRight) even as a reference. So what I did was added an if clause:</p>
  
  <pre><code>If Alternative = "" Then
      If ActiveCell &lt;&gt; Cells(1, 1).End(xlToRight) Then
          ActiveCell.End(xlToRight).Select
      End If
      Set comparator = ActiveCell.Next
      comparator.Activate
      ActiveCell.Value = Sheet2Header
      Exit Function
  End If
  </code></pre>
  
  <p>And set comparator equal to ActiveCell.Next</p>