Detemine which variables add up to a pre-determined sum and report?

[Sevael]

Hello,

I'm having trouble coming up with a way to determine which specific variables from a group add up to a specific number.

The macro will begin on a screen which displays a particular 7-digit value. It picks up this amount as a variable, let's call it TE. It will then scroll to subsequent pages and pick up a value off each page (we'll call these E1, E2, E3, etc). It then needs to report back which of these numbers total up to exactly equal TE.

The number of pages varies on each file we process and there could be as many as 20 pages in a single file. Due to the nature of the source material, there will always be only one possible combination that adds up to TE, so the first combination this macro finds that equals TE will always be the correct solution. Any E values that are greater than TE can be ignored, as subtraction is never a factor; these amounts can only be added together.

Hypothetical example:

TE = 100

E1 = 50
E2 = 30
E3 = 21
E4 = 119
E5 = 60
E6 = 12
E7 = 15
E8 = 8
E9 = 105
E10 = 86

The solution is E1 + E2 + E6 + E8. How do I get the macro to determine that combination on its own and report this back to the user?

The only way I know of is to write a line of code to sum every possible combination of E variables and just find the one that equals 100. But if we have as many as 20 of the E variables, the sheer number of possible combinations is beyond the scope of this macro.

How would I make this into a reasonable amount of code, including the ability to report the correct combination of E variables back to the user in a msgbox at the end? The format would need to be "E1 + E2 + E6 + E8" and not the actual numerical values.

Thanks in advance for any help provided to my problem.

 

[SkipVought]

Hi,

You really have 2 broad questions. The first question relates to how to do the math, while the second is how to write the code.

For the first, you need to pay a mathematition.

For the second, you need to pay a programmer.

I suggest you get started looking for both.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sevael]

This certainly isn't mathematician-level. It's a matter of setting up a sequence of variable addition and reporting the findings back to the user when it matches a certain value. I imagine it wouldn't be that tough for somebody versed in these macros.

I was able to do it myself fairly easily for up to 8 pages, but beyond that took too many lines of code and the macro refused to run. I am not familiar enough with these macros to streamline code or create decent arrays, which is why I am seeking help here. As the posters on this forum are very familiar with the coding and the tricks of the trade, I figured it reasonable that somebody might have an idea how to trim down redundant long-form into an array.

Thanks for your reply, Skip. I appreciate that you took the time to read and respond to my post. However, I'm not going to give up just yet. I don't think this is anywhere near as complex as you're making it out to be. No offense.

For reference, the following is essentially how I had the code working. Unfortunately I cannot copy and paste the actual code here, as it is at work in a secure environment with no internet connection. I'm writing this here from memory.

TE = GetString(?,?,7)

ECountMax = GetString(?,?,2)   'Grabs the number of the last
                               'page (page number is displayed
                               'on every screen as 1/10,
                               '2/10, etc)
ECount = 1   'Current page number, always begins from
             'the first page

For ECount = 1 To ECountMax
    ECount = GetString(?,?,2)   'Grabs the current page number
    E(ECount) = GetString(?,?,7)   'Grabs the E value
    SendKeys("<PF5>")   'Moves to the next screen
Next

'--Beginning of redundant code--
If E(1) = TE Then
    Report = "E1"
    GoTo ReportReady
End If
If E(1) + E(2) = TE Then
    Report = "E1, E2"
    GoTo ReportReady
End If
If E(1) + E(2) + E(3) = TE Then
    Report = "E1, E2, E3"
    GoTo ReportReady
End If

. . .

If E(3) + E(5) + E(6) + E(8) = TE Then
    Report = "E3, E5, E6, E8"
    GoTo ReportReady
End If

. . .

'--End of redunant code--

ReportReady:
MsgBox Report

End Sub

It was something like that.

Clearly this code is very inefficient. Accounting for only 8 pages/variables eats up over 1200 lines of code in the macro. I was hoping someone experienced could show me how to put the redudant code into some sort of array and hopefully allow more pages to be handled as a result.

 

[SkipVought]

Glad to see some effort on your part.

1. Load your E values in an array rather then individual variables. Use the ReDim statement to rezise each time you need to store a new value.

2. use a loop to sum and vary the array items you include in your sum. This will be a creative venture.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Sevael]

Would you be able to provide me with some really basic examples to give me something to expand on? I can usually get a handle on this stuff once I have the initial momentum.

This is another idea I was having earlier. I really don't know how to get out of the If statements and into a proper array for these situations.

'--Take every individual E value and see if it matches TE--
Start1a:
ECount = 1
E = E(ECount)

Start1b:
If ECount > ECountMax Then
    GoTo Start2a
End If
If E <> TE Then
    ECount = ECount + 1
    E = E(ECount)
    GoTo Start1b
Else
    Report = "E" & ECount
    GoTo ReportReady
End If


'--Add every combination of two E values to try to match TE--
Start2a:
EStart = 1
ECount = 1
E = E(Estart) + E(EStart + ECount)

Start2b:
If EStart + ECount > ECountMax Then
    EStart = Estart + 1
    If EStart = ECountMax Then
        GoTo Start3a
    End If
    ECount = 1
    E = E(EStart) + E(EStart + ECount)
End If
If E <> TE Then
    ECount = ECount + 1
    E = E(EStart) + E(EStart + ECount)
    GoTo Start2b
Else
    Report = "E" & EStart & ", E" & EStart + ECount
    GoTo ReportReady
End If


'--Add every combination of three E values to try to match TE--
Start3a:
EStart = 1
ESecond = EStart + 1
ECount = 1
E = E(EStart) + E(ESecond) + E(ESecond + ECount)

Start3b:
If ESecond + ECount > ECountMax Then
    EStart = Estart + 1
    ESecond = EStart + 1
    If ESecond = ECountMax Then
        GoTo Start4a
    End If
    ECount = 1
    E = E(EStart) + E(ESecond) + E(ESecond + ECount)
End If
If E <> TE Then
    ECount = ECount + 1
    E = E(EStart) + E(ESecond) + E(ESecond + ECount)
    GoTo Start3b
Else
    Report = "E" & EStart & ", E" & ESecond & ", E" ESecond + ECount
    GoTo ReportReady
End If


'--Add every combination of four E values to try to match TE--
Start4a:
EStart = 1
ESecond = EStart + 1
EThird = ESecond + 1
ECount = 1
E = E(EStart) + E(ESecond) + E(EThird) + E(EThird + ECount)

Start4b:
If EThird + ECount > ECountMax Then
    EStart = Estart + 1
    ESecond = EStart + 1
    EThird = ESecond + 1
    If EThird = ECountMax Then
        GoTo Start5a
    End If
    ECount = 1
    E = E(EStart) + E(ESecond) + E(EThird) + E(EThird + ECount)
End If
If E <> TE Then
    ECount = ECount + 1
    E = E(EStart) + E(ESecond) + E(EThird) + E(EThird + ECount)
    GoTo Start4b
Else
    Report = "E" & EStart & ", E" & ESecond & ", E" ESecond + ECount
    GoTo ReportReady
End If

. . .

ReportReady:
MsgBox Report

End Sub

Sorry for the sloppy code. I'm pretty new at this.

Does that look like it has any potential? It would still be lengthy, but it should fit within the size constraints of the macro system.

 

[IknowMe]

You sure this isn't a homework assignment?

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[IknowMe]

http://www.microsoft.com/communitie...14e-9881-2ba33de8be2f&lang=en&cr=US&sloc=&p=1

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[Sevael]

Nope, this is for work. I haven't been in school for nearly 15 years.

Thanks for the link. The code on that page is way beyond my skills. I've been picking up the basics from existing macros and trial-and-error making new ones. But this variable-addition-total one I'm trying to make now is at a new level.

At work yesterday I tried out a variant of the code I posted above. It works, but each section becomes increasingly larger and holds more combined If statements. It's not pretty, but that's my lack of experience showing through. So far it's up to 9 variables and takes up about 900 lines of code. The Extra macro system only works when you have somewhere between 1200 and 1300 lines; anything more and it errors out. I'll be able to squeeze a few more in there before I run out of room, but after that point I'll have to figure out how to reduce the repetition and use more GoTo statements, I guess.