title: Adding validation to a range of cells in Excel using VBA
body: <p>I am trying to make a &quot;dynamic table&quot; in Excel using VBA. It is about students, problems and their results. In row 5 i have the maximum possible points a student can get in the corresponding problem. So, i want every cell below it to have a data validation 0&lt;=value&lt;=max. So far i came up with the following code:</p>
  <pre><code>    With range(Chr(69 + i) &amp; &quot;6:&quot; &amp; Chr(69 + i) &amp; 5 + x).Validation
          .Delete
          .Add Type:=xlValidateWholeNumber, _
              AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, Formula1:=&quot;0&quot;, Formula2:=&quot;=F5&quot;
          .InputTitle = &quot;Partial points&quot;
          .ErrorTitle = &quot;Incorrect data&quot;
          .InputMessage = &quot;A value between 0 and max.&quot;
          .ErrorMessage = &quot;Should be a value between 0 and max!&quot;
      End With
  </code></pre>
  <p>For some reason, the upper bound (which sould be F5 for every cell in this case, just for debugging purpose) keeps incrementing with every row. It seems very strange, since i do not manipulate it elsewhere, and the formula, as it can be seen, is simple &quot;=F5&quot;. Any ideas what may cause this weird behaviour?</p>
  <p><a href="https://i.stack.imgur.com/QtVuL.png" rel="nofollow noreferrer">Here you can see the screenshot of the table.</a> If i check the data validation through Excel, the upper bound for row 6 is F5, for row 7 it's F6, for row 8 it's F7 and so on...</p>
  <p>The whole code, for anyone interested:</p>
  <pre><code>' inspiration : https://stackoverflow.com/questions/45274764/creating-a-table-based-upon-number-of-rows-columns-entered/45288195#45288195
  ' data validation done through plain excel : https://www.extendoffice.com/documents/excel/5938-excel-data-validation-allow-only-numbers.html
  ' worksheet stuff: https://stackoverflow.com/questions/6310240/switching-sheets-in-excel-vba
  ' grid: https://stackoverflow.com/questions/52417729/excel-vba-help-for-grid-lines
  ' x : number of students
  ' y : number of problems
  
  Sub CreateTable()
      ' swap to table
      Sheets(&quot;Táblázat&quot;).Activate
      
      Dim ws As Worksheet
      Set ws = Sheets(&quot;Beállítások&quot;)
  
      ' get new values
      Dim x As Integer
      Dim y As Integer
      x = ws.range(&quot;D3&quot;).Value
      y = ws.range(&quot;D4&quot;).Value
  
      ' get old values
      Dim oldX As Integer
      Dim oldY As Integer
      oldX = ws.range(&quot;B5&quot;).Value
      oldY = ws.range(&quot;C5&quot;).Value
  
      
      
      Set ws = Sheets(&quot;Táblázat&quot;)
      
      ' delete rows
      For i = 1 To oldX + 1
          ws.range(&quot;B6&quot;).EntireRow.Delete
      Next i
  
      ' insert rows
      ws.range(&quot;B6&quot;).EntireRow.Insert
      ws.range(&quot;C6:E6&quot;).Merge
      ws.Cells(6, 3).Value = &quot;Átlagok&quot;
      
      For i = 1 To x
          ws.range(&quot;B6&quot;).EntireRow.Insert
          ws.range(&quot;C6:E6&quot;).Merge             ' cell for name
          ws.Cells(6, 2) = &quot;D&quot; &amp; x - i        ' cell for index
      Next i
      
      ' only modify columns if needed (since it erases problem-related data)
      If oldY &lt;&gt; y Then
          ' delete all columns
          For i = 1 To oldY + 4
              ws.range(&quot;F4&quot;).EntireColumn.Delete
          Next i
          
          ' insert columns for statistics
          For i = 1 To 4
              ws.range(&quot;F4&quot;).EntireColumn.Insert
          Next i
          
          ' insert columns for grades
          For i = 1 To y
              ws.range(&quot;F4&quot;).EntireColumn.Insert
              ws.Cells(4, 6) = &quot;F&quot; &amp; y - i + 1
          Next i
      End If
      
      ' m - the column of sum
      ' Chr(64 + m) is letter of column m - ascii of A is 65
      Dim m As Integer
      m = 6 + y
      
      ws.Cells(4, m) = &quot;Össz&quot;
      ws.Cells(4, m + 1) = &quot;Százalék&quot;
      ws.Cells(4, m + 2) = &quot;Jegy&quot;
      ws.Cells(4, m + 3) = &quot;Osztályzat&quot;
      
      '' insert functions ''
      ' max sum
      ' example: =SUM(F5:K5)
      ws.Cells(5, m).Formula = &quot;=SUM(F5:&quot; &amp; Chr(63 + m) &amp; &quot;5)&quot;
              
      For i = 1 To x
          ' current row
          Dim n As Integer
          n = i + 5
          
          ' sum
          ' example: =SUM(F6:K6)
          ws.Cells(n, m).Formula = &quot;=SUM(F&quot; &amp; n &amp; &quot;:&quot; &amp; Chr(63 + m) &amp; n &amp; &quot;)&quot;
          
          ' percentage
          ' example: =ROUND(L6/L5*100,2)
          ws.Cells(n, m + 1).Formula = &quot;=ROUND(&quot; &amp; Chr(64 + m) &amp; n &amp; &quot;/&quot; &amp; Chr(64 + m) &amp; &quot;5*100,2)&quot;
              
          ' grade
          ' example: =ROUND(M6,-1)/10
          ws.Cells(n, m + 2).Formula = &quot;=ROUND(&quot; &amp; Chr(65 + m) &amp; n &amp; &quot;,-1)/10&quot;
              
          ' mark
          ' example: =IF(M6&gt;=Beállítások!E6,&quot;FB&quot;,IF(M6&gt;=Beállítások!E7,&quot;B&quot;,IF(M6&gt;=Beállítások!E8,&quot;S&quot;,&quot;I&quot;)))
          ws.Cells(n, m + 3).Formula = &quot;=IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n _
              &amp; &quot;&gt;=Beállítások!E6,&quot;&quot;FB&quot;&quot;,IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n _
              &amp; &quot;&gt;=Beállítások!E7,&quot;&quot;B&quot;&quot;,IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n _
              &amp; &quot;&gt;=Beállítások!E8,&quot;&quot;S&quot;&quot;,&quot;&quot;I&quot;&quot;)))&quot;
      Next i
      
      ' class average for each separete problem + add constraint
      ' example: =ROUND(AVERAGE(F6:F15),2)
      
  
      Dim wtf As String
      wtf = 0
      For i = 1 To y
          ws.Cells(n + 1, 5 + i).Formula = &quot;=ROUND(AVERAGE(&quot; &amp; Chr(69 + i) &amp; &quot;6:&quot; &amp; Chr(69 + i) &amp; 5 + x &amp; &quot;),2)&quot;
          ws.Cells(i, 1) = Chr(69 + i)
          With range(&quot;F5:&quot; &amp; Chr(69 + i) &amp; &quot;5&quot;).Validation
              .Delete
              .Add Type:=xlValidateWholeNumber, _
                  AlertStyle:=xlValidAlertStop, _
                  Operator:=xlGreater, Formula1:=&quot;0&quot;
              .IgnoreBlank = True
              .InputTitle = &quot;Maximális pont&quot;
              .ErrorTitle = &quot;Helytelen adat&quot;
              .InputMessage = &quot;Egy pozitív, nem nulla érték.&quot;
              .ErrorMessage = &quot;A maximális pont nagyobb mint 0!&quot;
          
          End With
              
          'for some reason 5 keeps incrementing
          With range(Chr(69 + i) &amp; &quot;6:&quot; &amp; Chr(69 + i) &amp; 5 + x).Validation
              .Delete
              .Add Type:=xlValidateWholeNumber, _
                  AlertStyle:=xlValidAlertStop, _
                  Operator:=xlBetween, Formula1:=&quot;0&quot;, Formula2:=&quot;=F5&quot;
              .InputTitle = &quot;Részpont&quot;
              .ErrorTitle = &quot;Helytelen adat&quot;
              .InputMessage = &quot;Egy érték 0 és maximális pont között.&quot;
              .ErrorMessage = &quot;A részpont egy érték 0 és maximális pont között!&quot;
          End With
          
      Next i
      
      For i = 1 To y
      Next i
      
      ' sum
      ' example: =SUM(F16:K16)
      ws.Cells(n + 1, m).Formula = &quot;=SUM(&quot; &amp; Chr(70) &amp; n + 1 &amp; &quot;:&quot; &amp; Chr(69 + y) &amp; n + 1 &amp; &quot;)&quot;
  
      ' percent
      ' example: =ROUND(L16/L5*100,2)
      ws.Cells(n + 1, m + 1).Formula = &quot;=ROUND(&quot; &amp; Chr(70 + y) &amp; n + 1 &amp; &quot;/&quot; &amp; Chr(70 + y) &amp; &quot;5*100,2)&quot;
  
      ' grade
      ' example: =ROUND(M16,-1)/10
      ws.Cells(n + 1, m + 2).Formula = &quot;=ROUND(&quot; &amp; Chr(65 + m) &amp; n + 1 &amp; &quot;,-1)/10&quot;
  
      ' mark
      ' =IF(M16&gt;=Beállítások!E6,&quot;FB&quot;,IF(M16&gt;=Beállítások!E7,&quot;B&quot;,IF(M16&gt;=Beállítások!E8,&quot;S&quot;,&quot;I&quot;)))
      ws.Cells(n + 1, m + 3).Formula = &quot;=IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n + 1 _
              &amp; &quot;&gt;=Beállítások!E6,&quot;&quot;FB&quot;&quot;,IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n + 1 _
              &amp; &quot;&gt;=Beállítások!E7,&quot;&quot;B&quot;&quot;,IF(&quot; _
              &amp; Chr(65 + m) _
              &amp; n + 1 _
              &amp; &quot;&gt;=Beállítások!E8,&quot;&quot;S&quot;&quot;,&quot;&quot;I&quot;&quot;)))&quot;
  
      ' draw borders
      Dim area As range
      Dim aux As String
      aux = &quot;B4:&quot; &amp; Chr(67 + m) &amp; (n + 1)
      
      For Each area In ws.range(aux).Areas
          With area.Borders
              .LineStyle = xlContinuous
              .Color = vbBlack
              .Weight = xlThin
          End With
      Next
  
      ' set alignment
      aux = &quot;F4:&quot; &amp; Chr(67 + m) &amp; n + 1
      range(aux).Select
      Selection.VerticalAlignment = xlCenter
      Selection.HorizontalAlignment = xlCenter
  
      ' set print area
      With ws.PageSetup
          .PrintArea = &quot;$A$3:$&quot; &amp; Chr(74 + y) &amp; &quot;$&quot; &amp; 7 + x
          .FitToPagesWide = 1
          .FitToPagesTall = 1
          .Orientation = xlLandscape
      End With
  
      
      
      Set ws = Sheets(&quot;Beállítások&quot;)
      
      ' update old values
      ws.Cells(5, 2).Value = x
      ws.Cells(5, 3).Value = y
      
      ' &quot;deselect&quot; previous selection
      range(&quot;A1&quot;).Select
      
  End Sub
  </code></pre>
  <p>Sorry for the things written in Hungarian, but this should not influence the code.</p>