title: Excel VBA swapping cells if value is an array
body: <p>Abigaylebury    South Carolina</p>
  
  <p>Abigaylebury    South Carolina</p>
  
  <p>South Carolina  Abigaylebury</p>
  
  <p>*idea is to automatically swap wrongly placed states with home towns .i.e south caroline &amp; abigaylebury </p>
  
  <p>i've got 5001 rows and trying to access in particular two columns (Home town, Home state). 
  I am trying to write a vba script that swaps the home town and home state values, if i have a home state value wrongly placed in the home town column . I've tried to do this by having an array that references a range of all 50 states inside the worksheet and should be checked against in the if statement. </p>
  
  <p>However i keep getting mismatch, define application defined or object defined error. novice errors im sure.</p>
  
  <p>this is my attempt:</p>
  
  <pre><code>Dim states() As String 
  states = Range("N1:N50").Value 
  Dim x As Long 
  Dim y As Long 
  
  'go through rows in the d column
  For x = 1 To 5001
      'Range("D" &amp; x).Select
  
          'for loop for array and if statement
              For y = 1 To 50
  
              states() = Cells(y, n)
  
                      If y &lt;= 1 Then
  
              'second for loop
  
                      Var = Cells(2, 4)
                      Cells(2, 4) = Cells(2, 5)
                      Cells(2, 5) = Var
  
                     End If
  
              Next y 'go to next 1..50            
  Next x
  </code></pre>