Button to add to existing values plus subtract a range

[cazan]

Greetings Programers,

I have be looking for some samples of how to use a button with a macro to add a value (let's say minutes) entered in cell A1.

Which, upon clicking the button adds the value in A1 to an existing value of (mins.) to cell A2. Also, returning cell A1 back to a zero value.

Once the total in A2 is updated, range (A3:F3) of positive values are subtracted from cell A2 and the results are displayed in cell B1(Mins. remaining).

I know this should be a simple one. But being new to VB, I need some help on this one. I thought I saw an example of this somewhere but can't seem to locate it.

Thank in advance,
Chris A. Z.

 

[Derek]

Hi cazan, Welcome to the Board,

This should work for the first part of your task:


[a2].Value = [a2].Value + [a1].Value
[a1].ClearContents

I do not understand the next part of your task:
"Once the total in A2 is updated, range (A3:F3) of positive values are subtracted from cell A2 and the results are displayed in cell B1(Mins. remaining)."

Are there negative numbers in this range? Is the total of all positive values in this range to be subtracted from A2 and the answer displayed in B1? If so, try this code

[a2].Value = [a2].Value + [a1].Value
[a1].ClearContents
[b1].Value = [a2].Value
For Each cell In [a3:f3]
If cell.Value > 0 Then [b1].Value = [b1].Value - cell.Value
Next
End Sub


If not, please clarify

regards
Derek

 

[cazan]

Hi Derek,

Thanks so much for the reply. The values of range A3:F3 are all positive integers. Basically, to give you an example of the flow process, let's use a tanning package for a frequent customer as an example.

The customer has purchased a package of 90 mins. of tanning. That would be added using the field/cell A1. Clicking the button would add to an existing total. Range A3:F3 represents the mins. used from the package with each appointment.

I neglected to mention that each cell representing an appointment has a spinner control added to it (for point and click ease). Down the road appointments represented by cells A3:C3 have depleted the 90 mins. which displays result of zero in cell B1.

The customer needs to purchase an additional package of 90 mins.

This takes us back to adding an additional 90 mins. in cell A1 and clicking the button to update the total. Now, thinking this through logically, code would have to be added to the spinner buttons to initial a recalculation/update every time the spinner button is used. Would that be correct?

I hope this explains the flow process a little better. It haunts me, but I know I have seen this somewhere before.

While I am thinking of it. Can you suggest a good CBT Software or Tech Book on VB for Excel? I think it would be good place for me to get my feet wet with VB programming. It shouldn't be too hard since I still use DOS for automating some processes.

 

[cazan]

Thanks Derek,

Putting the code you gave me to work, I think it will do the trick. I'll just use the Add Button to update any changes in the range. I'll just relabel the button to say "UPDATE". Unless, you have any other suggestions based on my reply to you, thank you again.

 

[cazan]

One thing I forgot to mention is that the cells being subtracted from the total minutes is not a continuous cell range but ever other cell in a row. Would your code require any adjustments?

 

[Derek]

Hi again cazan

You can change the code to indicate the range of cells whose positive values you want to deduct, for example:

[a2].Value = [a2].Value + [a1].Value
[a1].ClearContents
[b1].Value = [a2].Value
For Each cell In Range("a3,c3,e3,g3")
If cell.Value > 0 Then [b1].Value = [b1].Value - cell.Value
Next
End Sub

I have used a3, c3, e3 and g3 (replace with your cell names and just separate them with a comma).

Others on this site would be better qualified than me to recommend books on VB. I am looking for a really simple one myself, the reference books I have appear to need the IQ of an Einstein to comprehend. I am teaching myself by trial and error using the macro recorder and playing around with the recorded code. In many instances the answers can be obtained from the VB help files but when I really get stuck I turn to this Board for the admirable assistance that everybody is prepared to give. I learned more in the first 3 months reading the posts here than I learned elsewhere in the previous 6 years. I try to pick a problem from this site and solve it every day. Sometimes I get lucky, but more often I see another friendly guru has posted something quite brilliant and (quite unashamedly) I then pinch their clever answer and use it at work as my own (did not Einstein once say "The secret to being a genius is to hide one's sources"...or something like that).
Good luck
Derek

 

[cazan]

Hi Derek or anyone else up to the challenge,

Another thing I have been trying to figure out on my own since your last reply is to also record the values added. Not only will I need to add minutes to a total and subtract them as they are used, but record on a separate row in consecutive cells across the minutes I have been adding along the way, as below.

15 minutes added and recorded in A5
20 minutes added and recorded in B5
10 minutes added and recorded in C5
30 minutes added and recorded in D5

I thought it would be more of a paste if cell value = 0, cell value > 0 paste next cell over. Isn't this more of a loop? I have been looking for samples of code similar to this but haven't found it yet.

Have a good day,
Chris A. Z.