Excel Overflow Error

Question

Here is my code :

 Dim i As Integer, a As Integer, rowsInThere As Integer, rowsInI As Integer
 Dim ws As Worksheet, b As Integer
 Dim x As Integer, z As Integer
 Dim total As Integer
 Dim value As String
 rowsInProjects = Sheets("Projects").UsedRange.Rows.Count
 z = 3


Worksheets("Summary_Sheet (2)").Range("b5:b50").ClearContents
Worksheets("Summary_Sheet (2)").Range("c5:c50").ClearContents
Worksheets("Summary_Sheet (2)").Range("d5:d50").ClearContents
Worksheets("Summary_Sheet (2)").Range("e5:e50").ClearContents
Worksheets("Summary_Sheet (2)").Range("F5:F50").ClearContents
Worksheets("Summary_Sheet (2)").Range("G5:G50").ClearContents
Worksheets("Summary_Sheet (2)").Range("H5:H50").ClearContents




For a = 1 To rowsInProjects
  value = Worksheets("Projects").Cells(a, 1).value
  Worksheets("Summary_Sheet (2)").Cells(a + 4, 2).value = value

    For i = 5 To Worksheets.Count
        rowsInI = Worksheets(i).UsedRange.Rows.Count
            For x = 1 To rowsInI
                If Worksheets(i).Cells(x + 8, 3).value = value Then
                    total = total + Worksheets(i).Cells(x + 8, 6).value
                End If
                Worksheets("Summary_Sheet (2)").Cells(i, z).value = total
            Next x
        z = z + 1
    Next i
    z = 3
Next a

There error arises on the total = total + ... line. What my code is doing is copying a list of projects from a worksheet into a new one.

It then has to search through the other worksheets for each of the project names added. Each of the other worksheets will have 2-3 records with the project name. I want to get the total cost of the project from each worksheet and then insert it back into the orginal file.

Steps: 1. Create list of projects

2. Iterate through List

3. iterate through each worksheet

4. totaling values from matching projects

5. Insert value back into project list

J O'Brien, Choate and Townsend are the 3 worksheets

This is the Choate worksheet

Is this approach right for what I am trying to achieve?

Answer 1

You also asked if this (your method) was the right approach, Yours works so yes it is. However, it could possibly be optimised.

For every item in your project list, you're iterating over every row in your data sheet in all of your data sheets.

Which, depending on the number of rows in each sheet, is a fair few! (its at least Projects * Rows * 3)

I dont know if your "projects" list is one you generate per-run, so you only get a few projects or if its just everything you've got.

Hopefully the code below makes some sense, if you decide to give it a go please make sure you run it on a copy of your data! It's an example and it only dumps the result to the debug window.

The code below (which may not function perfectly as I might have got the columns and rows wrong) will loop over each sheet once and calculate a per-sheet total for each project (allowing for multiple instances of the same project in a single sheet, if this is possible in your data)

Sub Main()
Dim Projects As Object
'Dim Projects As Scripting.Dictionary
Set Projects = CreateObject("Scripting.Dictionary")
'Set Projects = New Scripting.Dictionary

Dim Project As String
Dim Sheets() As String
Dim Name As String
Dim Sheet As Worksheet
Dim SheetIndex As Integer

Dim ProjectColumn As Variant
Dim TotalColumn As Variant

Dim Index As Integer
Dim Max As Long
Dim MaxRow As Long

' You'll need to put your sheet names below
' not very nice, just a way to predefine an array containing sheet names
Sheets = Split("Sheet1,Sheet2,Sheet3", ",")

' loop over all the sheets
For SheetIndex = 0 To UBound(Sheets)
    ' get a reference to the sheet were looking at
    Set Sheet = ThisWorkbook.Worksheets(Sheets(SheetIndex))
    ' calculate the last row in the workbook (as using UsedRange isnt always right)
    MaxRow = Sheet.Cells(Sheet.Rows.Count, 3).End(xlUp).Row
    ' get the data were looking for
    ' the 9 in the next 2 lines might be wrong, it should be the row# for the first data row
    Set ProjectColumn = Sheet.Range(Sheet.Cells(9, 3), Sheet.Cells(MaxRow, 3)) ' the 9 here might be wrong! 
    Set TotalColumn = Sheet.Range(Sheet.Cells(9, 6), Sheet.Cells(MaxRow, 6)) ' again, 9


    Max = MaxRow - 8 ' adjust the max row to account for the +8 header cells above the data

    For Index = 1 To Max
        ' loop over all the projects in the current sheet
        Project = ProjectColumn(Index, 1)
        ' this allows for multiple instances of the same project per sheet (no idea if this occurs in your data)
        If Projects.Exists(Project) Then
            If Projects(Project).Exists(Sheets(SheetIndex)) Then
                ' update the total
                Projects(Project)(Sheets(SheetIndex)) = Projects(Project)(Sheets(SheetIndex)) + TotalColumn(Index, 1)
            Else
                ' inclue the total for the sheet
                Projects(Project).Add Sheets(SheetIndex), CLng(TotalColumn(Index, 1))
            End If
        Else
            ' new project, add it and the total value for the current sheet
            'Projects.Add Project, New Scripting.Dictionary
            Projects.Add Project, CreateObject("Scripting.Dictionary")
            Projects(Project).Add Sheets(SheetIndex), CLng(TotalColumn(Index, 1))
        End If
    Next Index

    Set ProjectColumn = Nothing
    Set TotalColumn = Nothing

Next SheetIndex

' Projects now contains a list of all projects, and the totals for your sheets.

' Projects
' - Project Name
' - - Sheet Name - Sheet Total

' dump the data to the immediate window in the vba editor
For Each Key In Projects.Keys
    For Each SubKey In Projects(Key).Keys
        Debug.Print Key & ", " & SubKey & " = " & Projects(Key)(SubKey)
    Next SubKey
Next Key

End Sub

Using this you'd only need to iterate over each sheet once and then one further iteration over the projects sheet to extract the required totals from the result.

Answer 2

My normal approach is to hold all the available data in one worksheet so I can analyse it using pivot tables. If I then need to create a non-pivot table worksheet, I then use VBA to copy and PasteSpecial the pivot table as a regular range.

If there's a really good reason to hold this data in 3 separate worksheets, I would amalgamate the 3 sheets using VBA (basically copying and pasting all the data into a single worksheet with one set of column headers), adding an extra column that contains either "Choate", "OBrien" or "Townsend" during the copy/paste process, then creating a pivot table from the resulting amalgamation.

I use this approach a lot - all my data is standardised and I can filter the pivot table as required - by date, project, manager/salesperson/creator (Choate, Townsend and O'Brien), currency, or whatever.

I'm afraid this isn't strictly speaking an answer to your question, more a suggestion of a different approach. Of course, I don't know the circumstances of how you get this data, so it may not be feasible for you.

Answer 3

you're probably overflowing the max size of an Integer, which is 32767. Try using a long for your range loop counter instead. This would apply to a, x, z and rowsInI