title: Pivot Cache Type Mismatch error VB.NET Excel
body: <p>Please see bottom edit for where I am currently at, thank you.</p>
  
  <p>I have created a pivot table that works fine when the pivot cache is defined as:</p>
  
  <pre><code>        Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:v7500"))
  </code></pre>
  
  <p>My problem is that the number of rows changes from day to day, so I figure out the number of rows in the worksheet and then use that in my pivot cache:</p>
  
  <pre><code>Dim highlRow As Integer
  highlRow = mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row
  Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" &amp; highlRow))
  </code></pre>
  
  <p>This solution has worked great for me historically, but when I do it here, I get: </p>
  
  <pre><code>Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
  </code></pre>
  
  <p>I have been fiddling around with it a fair bit and found that if I do:</p>
  
  <pre><code>highlRow = 7000
  Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" &amp; highlRow))
  </code></pre>
  
  <p>It works fine, so I am left wondering what is the problem with how I define highlRow using the specialcells command and store it as an integer. I feel like there is some kind of type or casting issue here, but I can't put my finger on it. I tried:</p>
  
  <pre><code>highlRow = CType(mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row, Integer)
  </code></pre>
  
  <p>And that was no good either. Perhaps my method for finding the last row is just not applicable here? Anyway, I have spent a long time tinkering with it and I am just spinning my wheels at this point. If you have any thoughts it would be much appreciated.</p>
  
  <p>Thank you as always SO.</p>
  
  <p>edit: I also tried out:</p>
  
  <pre><code>UsedRange.Rows.Count
  </code></pre>
  
  <p>Which has worked historically and it was also not able to run. At this point I am very curious as to why pivot cache specifically seems to have this issue.</p>
  
  <p>@charles had a good idea to try other ways of getting the last cell. I tried:</p>
  
  <pre><code>highlRow = mainHighwayData.Range("A65535").End(XlDirection.xlUp).Row
  </code></pre>
  
  <p>I got it to give the correct rows, but it still throws the type mismatch error.</p>
  
  <pre><code>highlRow = mainHighwayData.Cells.Find("*", SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlPrevious).Row
  </code></pre>
  
  <p>This returns the proper number for the last row, but once again throws the same type mismatch error.</p>
  
  <p>Edit: I found out another bit of information, but I am not sure what to do with it. The pivot table works fine if the values in it are &lt;= 65536, but the second I increase the range to 65537 I get the type mismatch that has been haunting me. This is true for all numbers >= 65537. I know that 65535 or there abouts used to be the last row in excel, but that is no longer the case. Also when I create the pivot table manually in excel I have no trouble and it has all of the data. I am using int or long, so it should not be an overflow or anything. Anyone have any thoughts on why VB.NET will not let me make a pivot table based on data with more than 65537 rows?</p>