1000 to 0

[DanKOzz]

So this is what I'm trying to figure out...

For example, if I had 1000 in one cell and in the cell below it I enter =RANDBETWEEN(90,122)... then in the cell below that, I would like to see the answer be depleted to 0 by pressing the F9 key repeatedly. I want to keep this all to just 3 cells but no more than 5.

What do I need to do to make this happen?

A preemptive thank you

 

[GlennUK]

What does this "I want to keep this all to just 3 cells but no more than 5." mean?

Can you explain the purpose of what you are asking for?

 

[DanKOzz]

I want to limit the amount of cells used to keep the spread sheet clean looking.

 

[GlennUK]

So not just 3 cells then. Gee, thanks for the confusing message.

You haven't answered my other question ... what is the purpose of what you are trying to do?

 

[DanKOzz]

I apologize for the confusion

What I am trying to do is make a text based RPG with excel. The part i'm stumped on is some of the combat elements of the game i'm trying to make. The 1000 represents a target being at full health. 0 is the goal i'm trying to meet, of course meaning the target is dead.

Hope that helps... Again, thank you very much for any help you can lend to me.

 

[GlennUK]

So you might have this on your sheet:

Excel Workbook
	A	B
1	Initial strength	1000
2	Attack depletion	103
3	Remaining strength	0
Sheet

You could then have this in your sheet code area ( right-click sheet tab, and choose View Code ):

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Range("B3").Value = "" Then
        Range("B3").Value = Application.Max(0, Range("B1").Value - Range("B2").Value)
    Else
        Range("B3").Value = Application.Max(0, Range("B3").Value - Range("B2").Value)
    End If
    Application.EnableEvents = True
End Sub
Sub reset_remaining_strength()
    Application.EnableEvents = False
    Range("B3").ClearContents
    Application.EnableEvents = True
End Sub

You'd reset B3 to blank by calling reset_remaining_strength macro.

 

[DanKOzz]

Awesome Sir!!! It does exactly what I want.

I greatly appreciate your help

 

[GlennUK]

That's great! Glad to help.