title: Visual Basic, VBA array loop
body: <p>I used <a href="http://www.homeandlearn.org/arrays_and_loops.html" rel="nofollow noreferrer">http://www.homeandlearn.org/arrays_and_loops.html</a> to help me with this.</p>
  
  <p>I have data connections that filter to one table. Unfortunately one of the sources randomly places incorrect data (usually a date) in the wrong column (Client column) when the program exports the file to Excel. What I'd like to do is something similar to an index/match function. I'd like to check each reservation number in this master table (A) against another table (B) within the same workbook. If the reservation number from the other sheet (B) matches the reservation number in the master table (A), I would like to have the correct Client value entered into the master table (A). I'm still pretty new to VBA so any help is appreciated. I've tried to modify my code here and there but to no avail. Also, I was originally running this as a practice without the real data so I didn't mess up my original file. I tried to add the appropriate syntax to refer to other sheets and whatnot so I suppose that could be entered incorrectly as well. Here's the closest original code I came up with:</p>
  
  <pre><code>Sub TransferData()
  Dim MyArray(1 To 19) As Single
  
  MyArray(1) = 81899
  MyArray(2) = 87172
  MyArray(3) = 87275
  MyArray(4) = 87394
  MyArray(5) = 87446
  MyArray(6) = 87496
  MyArray(7) = 87621
  MyArray(8) = 87631
  MyArray(9) = 87726
  MyArray(10) = 87822
  MyArray(11) = 87858
  MyArray(12) = 88041
  MyArray(13) = 88097
  MyArray(14) = 88127
  MyArray(15) = 88160
  MyArray(16) = 88191
  MyArray(17) = 88359
  MyArray(18) = 88487
  MyArray(19) = 88545
  
  For i = 1 To 19
      If Worksheets("Sheet1").Range("B" &amp; i).Value = MyArray(i) Then
         Worksheets("Sheet2").Range("P" &amp; i).Value = _ 
         Worksheets("Sheet1").Range("E" &amp; i).Value
      End If
  Next i
  
  End Sub
  </code></pre>
  
  <p>I don't recall the error because the code wasn't exactly as above but close to it. I believe the issue it was running into was that when the i variable went above 19, the system couldn't find arrays > 19. I need VBA to check 19 arrays in an ever changing number of rows that's currently at 3k+. I attempted to add another variable thinking if I kept the variables separate, I could have VBA check the 19 arrays against all the rows. Here's the code I came up with for that....</p>
  
  <pre><code>Sub TransferData()
  Dim MyArray(1 To 19) As Single
  
  MyArray(1) = 81899
  MyArray(2) = 87172
  MyArray(3) = 87275
  MyArray(4) = 87394
  MyArray(5) = 87446
  MyArray(6) = 87496
  MyArray(7) = 87621
  MyArray(8) = 87631
  MyArray(9) = 87726
  MyArray(10) = 87822
  MyArray(11) = 87858
  MyArray(12) = 88041
  MyArray(13) = 88097
  MyArray(14) = 88127
  MyArray(15) = 88160
  MyArray(16) = 88191
  MyArray(17) = 88359
  MyArray(18) = 88487
  MyArray(19) = 88545
  
  For i = 1 To 5000
  For j = 1 To 19
  If Worksheets("Sheet1").Range("B" &amp; i).Value = MyArray(j) Then
      Worksheets("Sheet2").Range(i, 16).Value = Worksheets("Sheet1"). _ 
      Range(i,5).Value
      Next j
  End If
  Next i
  
  End Sub
  </code></pre>
  
  <p>With this code I get compile error: Next without For. In searching online I found it might be because I have 2 "For"s, an if statement, "next" statement within the if statement, then another "next" statement outside of the loop. I was thinking it had to be done this way so that each cell in the B column gets checked against all the array possibilities.</p>
  
  <p>See pictures below. I need the value of Column P (Actual Billing Name) from sheet: TMRtoSPIde to be entered into Column D (Billing Name) on sheet: RawData when the Reservation # in Column K from sheet: TMRtoSPIde matches the reservation in sheet: RawData. You'll notice the sheet: RawData has an erroneous 5 digit serial date in the Billing Name column. These are what I'm trying to replace.</p>
  
  <p><a href="https://i.stack.imgur.com/y2s51.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/y2s51.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/jIzUo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jIzUo.png" alt="enter image description here"></a></p>