Adding a value to a cell(A1) and putting the resulting total back into A1

**Masrim** · 02-24-2011, 04:55 PM

Hi all, first time here and I am looking for a little help with a small pet project of mine.

Essentially what I am trying to accomplish is this:

A1 = # 
A2 =RANDBETWEEN(1,6)

What I want to happen is that a1 starts at a number (can be anything) and every time I hit F9(calculate) I want the random number in a2 to be added to the total in a1.

So if a1 = 0
Then I get a random number of 4 in A2
I want A1 = 4 now
Then I want to hit F9 again to get a number random number, say 3 this time and have A1 change to 7 and so on.

Is this possible or should I just give up now?

I have little to no experience in VBA but I learn fast so with a little guidance I should be good.

Thanks!

**Simon Lloyd** · 02-24-2011, 05:07 PM

Well it will have to be VBA but you will have to make sure that your calculation is set to manual:
1. Right Click sheet tab
2. Choose "View Code"
3. Paste in the below

Private Sub Worksheet_Calculate()
Me.Range("A1").Value = Me.Range("A1").Value + Me.Range("A2").Value
End Sub

**Masrim** · 02-24-2011, 05:35 PM

Excellent so this will cycle every time I hit F9 then?

**Simon Lloyd** · 02-24-2011, 05:40 PM

Yes as long as you have TOOLS>OPTIONS>CALCULATION set to manual then hitting F9 will cause the increase

**Masrim** · 02-24-2011, 09:09 PM

Excellent, thank you very much Simon!

**Masrim** · 02-25-2011, 12:23 PM

Well I have tried expanding on your bit of code there but I am having troubles with some if statements.

Essentially I want to perform different actions based the result of the random number. This is what I have pieced together trying to extrapolate from your code.

Private Sub Worksheet_Calculate()
#If Range("p16").Value = 0 Then
    #If Range("f34").Value > 3 Then
        #If Range("f34").Value < 11 Then
            #If Range("f34").Value <> 7 Then
                Range("p16").Value = 1
                Range("p17").Value = Range("f34").Value
            #End If
        #End If
    #End If
#Else
    #If Range("p16").Value = 1 Then
        #If Range("f34").Value = 7 Then
            Me.Range("q16").Value = Me.Range("q16").Value - Me.Range("l16").Value
        #Else
            #If Range("f34").Value = Range("p17").Value Then
                Range("p16").Value = 0
                Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
            #Else
                Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
            #End If
        #End If
    #End If
#End If

I have also played around using a select case

Private Sub Worksheet_Calculate()

Select Case Range(f34).Value
    Case 7
        #If Range(p16).Value = 1 Then
           Me.Range("q16").Value = Me.Range("q16").Value - Me.Range("l16").Value
           Me.Range("p16").Value = 0
        #End If
    Case Else
        #If Range(p16).Value = 0 Then
            #If 3 < Range(f34).Value < 11 Then
                Me.Range("p17").Value = Me.Range("f34").Value
                Me.Range("p16").Value = 1
            End If
        Else
            #If Range(f34) = Range(p17) Then
                Me.Range("p16").Value = 0
                Me.Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
            Else
                Me.Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
            End If
        End If
End Select

End Sub

However I do not seem to be able to use the cells. I have tried various things, tried to find it in the help and on google but could not find anything clear.

So any help on this would be appreciated.

Thanks!

**Simon Lloyd** · 02-25-2011, 01:57 PM

I have no idea what you are trying to do or why you had 17 "#"'s in your code but here it is cut down for what i could tell was going on

Private Sub Worksheet_Calculate()
If Me.Range("p16").Value = 0 Then
    If Me.Range("f34").Value > 3 And Me.Range("f34").Value < 11 And Me.Range("f34").Value <> 7 Then
                Me.Range("p16").Value = 1
                Me.Range("p17").Value = Me.Range("f34").Value
    End If
ElseIf Me.Range("p16").Value = 1 And Me.Range("f34").Value = 7 Then
            Me.Range("q16").Value = Me.Range("q16").Value - Me.Range("l16").Value
ElseIf Me.Range("f34").Value = Me.Range("p17").Value Then
                Me.Range("p16").Value = 0
                Me.Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
            Else
                Me.Range("q16").Value = Me.Range("q16").Value + Me.Range("l18").Value
End If

End Sub

**Masrim** · 02-25-2011, 02:20 PM

Hi Simon,

The problem was all those #'s. lol

Like I said I am pretty new to VBA, seems I saw them somewhere and thought you needed them as part of the code. Once I removed them everything worked perfectly.

If you are truly curious the code is to run a craps table tallying place bets.
I might expand it to handle more of the betting options if I have the time. but as it stands this works well.

Thanks again for all your help!