1

To alleviate my summer boredom, I decided to try to make an Excel program that keeps track of how much money someone would owe based on properties owned and number of houses on those properties in the board game Monopoly (some new rules I'm experimenting with). I set up the spreadsheet such that every property is in its own row. I want the Excel program to do this:

Let's say you're looking at a cell. Then, For i=1 To 10 (since I doubt more than 10 players in Monopoly is feasible), if the value of the cell to the right is i, then add the value of the cell you're actually looking at to a sum for player i.

Can I generate variables variables sum1, sum2, sum3, ... , sum10? And then can I say something like

sum'i' = sum'i' + cell.Value

within a for loop if the condition in paragraph 2 is met?

UPDATE: I have tried this code, and it works in getting Player 1's sum. However, it gives Player 1's sum for all players, and it's cumbersome to update whenever someone buys new property or upgrades current property (I need to re-type the formula with each update).

Public Function monopolySum(i As Integer) As Variant

   Dim sum(1 To 10) As Variant, rng As Range, cell As Range

    For i = 1 To 10
        sum(i) = 0
    Next i

    Set rng = Range("C2:C29")

    For Each cell In rng
        For i = 1 To 10
            If cell.Offset(0, 1).Value = i Then
                sum(i) = sum(i) + cell.Value
            End If
        Next i
    Next cell

    For i = 1 To 10
        monopolySum = sum
    Next i

End Function

Thanks in advance everyone! I have very little experience programming and would greatly appreciate any guidance :)

Raman
  • 13
  • 4

1 Answers1

0

You are looking for arrays:

Dim varSum(1 To 10) As Variant
'Access it like this:
varSum(i) = xyz

Updated Answer
I see several improveable points:

  1. You are giving i as parameter for your function, but reassign values later. Name your parameter better iPlayer (what I assume it is needed for)
  2. You want to calculate the sum for a specific player? Then you dont need an array and calculate the sums for all players.
    EDIT (To be honest: You dont need VBA for this. Take a look at SUMIF)
  3. You want to calculate the sum for all players, say one big sum? Then you have to write monopolySum = sum(i) in the last For-Loop.
    EDIT (Here you could use SUM, but I think you know that)
  4. You could give the desired range as parameter to the function, so you dont have to set it "hard" by the function. Try this Public Function monopolySum(iPlayer As Integer, rngGame As Range) As Variant
  5. The answer for your initial question was correct? Then it should be accepted :)
Pira
  • 46
  • 5
  • Thanks @Pira! I made it a public function, but I can't call it in a cell within the worksheet, for some reason. – Raman Jul 03 '15 at 21:51
  • Hi @Raman, are you sure you have used `Function` instead of `Sub` as in your question? (You stated that you have very littlt programming experience :) If you have used `Function` and it doesnt works, look here: [Can't use VBA functions in a spreadsheet formula in Excel 2010](http://stackoverflow.com/questions/12351339/cant-use-vba-functions-in-a-spreadsheet-formula-in-excel-2010) (By the way: I think you have to put the second paragraph of your code example into either `Sub` or `Function` – Pira Jul 04 '15 at 01:21
  • Thanks for the tip, @Pira! I changed the code, but I have a new problem (see "UPDATE"). What do you think? – Raman Jul 04 '15 at 06:23
  • Ok, take a look at my update. I'm sorry, but I have no solution for the recalculation problem... – Pira Jul 04 '15 at 08:27
  • I tried SUMIF like you said, and it works like a charm; no VBA necessary. Oops, haha. Thank you so much for your help, @Pira! – Raman Jul 04 '15 at 21:55