Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,260
Office Version
2013
Platform
Windows
Hey Akuini,

While the code works absolutely brilliant, when I add a big volume, I get an error saying "Error found:Overflow"

Any idea?
Could you be more specific with what you mean by a big volume?


Try replacing all "Integer" with "Long" in this part:

Sub CombinationsNP2(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer


so it becomes:

Sub CombinationsNP2(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Long, iIndex As Long)
Dim i As Long
 

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
Could you be more specific with what you mean by a big volume?
When I tried to run this macro on rows about 60000 rows, It ran once and in next few runs, I got an error of Overflow.

I will try to replace "integer" with "long" as per your suggestion.
 

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
Hi Akuini,

I tried to replace Integer with Long, however still no luck.
Strangely I could execute the old Macro on huge data. Not sure why sometimes it does not run and five overflow error.

One more thing, for 1 job, there can be multiple combinations which are marked as contra. Can this Macro highlight which all combinations are contra with each other? For example, if I have 500 lines of which, 300 are contra, Can i know within these 300, which rows are contra against which rows?
 

Forum statistics

Threads
1,078,437
Messages
5,340,277
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top