Databases
StackOverflow2013
Postsdbo
POExcel VBA returning weird results with large calculations
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA returning weird results with large calculations
    primarykey
    data
    text
    <p>I've created a function in excel which basically searches a dynamic range in a For statement for a string and returns the value of the cell one column over. It's basically a budgeting function, but that's beside the point.</p> <p>Here's the problem, everything works with small results, but when the results get too large (say around 32000... for some reason that appears to be the number) the function starts returning <strong>0</strong>.</p> <p>Has anybody had problems like this? </p> <p>Here's the code in question:</p> <pre><code>Function Material(item As String, Optional sheetType As String) As Integer Dim wSheet As Worksheetr Dim count As Integer Dim offSet As Integer Dim ref As Integer Dim bottomRight As Integer Dim upperLeft As Integer Dim rng As Range Dim cVal As Integer For Each wSheet In Worksheets If wSheet.Name = "Drywall Pricing" Then dwIndex = wSheet.Index - 1 End If Next wSheet If IsMissing(sheetType) Then sheetType = " " Else sheetType = UCase(sheetType) End If For i = 1 To dwIndex wSheetName = Sheets(i).Name If InStr(UCase(wSheetName), sheetType) &gt; 0 Then count = 9 offSet = 44 ref = 27 For wall = 0 To count - 1 On Error Resume Next Err.Clear upperLeft = (ref + 12) + (offSet * wall) bottomRight = (ref + 30) + (offSet * wall) Set rng = Sheets(i).Range("A" &amp; upperLeft &amp; ":B" &amp; bottomRight) cVal = Application.WorksheetFunction.VLookup(item, rng, 2, False) If Err.Number &gt; 0 Then cVal = 0 End If units = units + cVal Next wall Else units = units + 0 End If Next i If units &gt; 0 Then Material = units Else Material = 0 End If End Function </code></pre> <p>I've set up a spreadsheet to manually count a certain number of items ( i.e. "=A13+B5+B26" etc), and compared it to the result of running the function in question, and when the results are low, they are equal to each other, so I know that the function itself is working correctly. Is this a memory issue, then?</p> <p>Any help would be greatly appreciated.</p> <p>Thanks in advance!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbschaeffer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExcel VBA returning weird results with large calculations
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POExcel VBA returning weird results with large calculations
      1. USMike Kellogg
      1. VTFavorite
    3. VO
      singulars
      1. POExcel VBA returning weird results with large calculations
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAny reason why you didn't Dim units?
      singulars
      1. POExcel VBA returning weird results with large calculations
      1. USJeffO
    2. CONo... It was an error on my part.
      singulars
      1. POExcel VBA returning weird results with large calculations
      1. USbschaeffer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload