VBA newbie- need help

[ljkeefe]

Hello Everyone,
I was initially tasked with creating a formula for this particular operation. However, the more I researched the more I realized that a formula alone would not produce the function that is being asked for. I'm interested in learning VBA but for the sake of time, I'd like to get this to my department without further delay. I just don't know anything about coding. Help with VBA would be great or even a referral to some sources to help me would be great as well.
Here's the scenario:
We have a truck with two tanks of the same material, CCB. We would like to track the quantity and cost of the CCB used in a season. The truck is set up to draw from Tank 2(column K) and once depleted it moves on to Tank 1(column J). When the tanks are replenished it isn't always to capacity, each hold 8000 gallons. Tank 1 will be filled and the remainder will go into Tank 2, but once operations begin it draws from Tank 2 again until depleted. We run in two shifts, the amount used would be entered at the end of every shift in column D and we'd like tank columns to update (column K & J). Here's what we are looking at.
Thank you!

 

[Yongle]

Welcome to the forum

What exactly are you trying to do ? What do you want to achieve ?
- I have read your post 3 times and cannot see a request or a question

 

[ljkeefe]

I need a VBA code to calculate the amount of CCB used per shift(which would be one entry per shift) entered into column D(starting at D10), this would deduct from Tank 2 (column K10) which begins with 8000. Once Tank 2 is 0, then any entry into column D would deduct from Tank 1 (column J10). If during a shift the tanks are replenished that amount would go into column H and therefore, added to Tank 1 and Tank 2. After replenishment, the amount entered into CCB (column D) would deduct from Tank 2 again, then start the cycle again. The end goal would be to track how much CCB is being used in a shift as well as give the following shift a visual of how much is left in the tanks.
I hope that makes sense.

 

[Yongle]

Perhaps you could provide some sample data and expected results using add-in XL2BB so that anyone trying to help understands what you want
Click on XL2BB icon above post window and read all the notes in that link (there are issues that you should be aware of)
The button to download the app is in the top right corner of the linked page

 

[ljkeefe]

ok. thank you. will do that.

 

[ljkeefe]

I need a VBA code to calculate the amount of CCB used per shift(which would be one entry per shift) entered into column D(starting at D10), this would deduct from Tank 2 (column K10) which begins with 8000. Once Tank 2 is 0, then any entry into column D would deduct from Tank 1 (column J10). If during a shift the tanks are replenished that amount would go into column H and therefore, added to Tank 1 and Tank 2. After replenishment, the amount entered into CCB (column D) would deduct from Tank 2 again, then start the cycle again. The end goal would be to track how much CCB is being used in a shift as well as give the following shift a visual of how much is left in the tanks.
I hope that makes sense.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Material application
2
3				Totals
4				Gallons of CCB	Sand	Sno melt	Salt	CCB Replenishment
5				0	0	0	0	0	Total Cost
6			Costs (Approximate)	$0.00	$0.00	$0.00	$0.00		$0.00
7
8	Date	Truck	Operator	CCB	Sand	Sno Melt	Salt	CCB Replenishment	CCB Available	Tank 1	Tank 2
9									16,000	8000	8000
10
11
12
Working Copy

Cell Formulas
Range	Formula
D5, H5	D5	=SUM(D10:D85)
E5:G5	E5	=SUM(E9:E85)
D6	D6	=D5*2.18
E6	E6	=E5*25
F6	F6	=F5*15
G6	G6	=G5*100
I6	I6	=D6+E6+F6+G6
J9	J9	=IF(I9>8000,8000)
I10:I12	I10	=IF(D10>0,(I9-D10),"")

Named Ranges
Name	Refers To	Cells
'Working Copy'!_FilterDatabase	='Working Copy'!$A$8:$G$50	D5:G5, I10:I12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I9:I150	Cell Value	<=6000	text	NO
I9:I150	Cell Value	<=11000	text	NO

 

[Eric W]

If I understand correctly, you might be able to use formulas. Consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Material application
2
3				Totals
4				Gallons of CCB	Sand	Sno melt	Salt	CCB Replenishment
5				24200	0	0	0	14000	Total Cost
6			Costs (Approximate)	52756	0	0	0		52756
7
8	Date	Truck	Operator	CCB	Sand	Sno Melt	Salt	CCB Replenishment	CCB Available	Tank 1	Tank 2
9									16,000	8000	8000
10				2000					14000	8000	6000
11				3500					10500	8000	2500
12				4000					6500	6500	0
13				2200					4300	4300	0
14								9000	13300	8000	5300
15				1800					11500	8000	3500
16				9000					2500	2500	0
17								5000	7500	7500	0
18				1700					5800	5800	0
Sheet2

Cell Formulas
Range	Formula
D5, H5	D5	=SUM(D10:D85)
E5:G5	E5	=SUM(E9:E85)
D6	D6	=D5*2.18
E6	E6	=E5*25
F6	F6	=F5*15
G6	G6	=G5*100
I6	I6	=D6+E6+F6+G6
J9	J9	=IF(I9>8000,8000)
I10:I18	I10	=IF(D10>0,(I9-D10),I9+H10)
J10:J18	J10	=IF(D10>0,J9-MAX(0,D10-K9),IF(H10>0,MIN(J9+H10,8000),J9))
K10:K18	K10	=IF(D10>0,MAX(0,K9-D10),IF(H10>0,K9+H10-(J10-J9),K9))

Put the J10 and K10 formulas in and copy down. As you enter number in columns D and H, the totals will adapt as I believe you want. I tweaked the I10 formula too.

 

[ljkeefe]

Thanks, Eric. Apparently, I was making it much harder than it was. It seems to work. Thanks!

 

[ljkeefe]

After testing this out this morning, I have two questions: (1) How can I modify the formula to add column H to the calculation if data is entered into column D? (2) How can I have "0" display in columns J & K until data is entered into the rows?

 

[Eric W]

In thinking over your questions, I believe we can use a simpler set of formulas. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Material application
2
3				Totals
4				Gallons of CCB	Sand	Sno melt	Salt	CCB Replenishment
5				24200	0	0	0	14000	Total Cost
6			Costs (Approximate)	52756	0	0	0		52756
7
8	Date	Truck	Operator	CCB	Sand	Sno Melt	Salt	CCB Replenishment	CCB Available	Tank 1	Tank 2
9									16,000	8000	8000
10				2000					14,000	8,000	6,000
11				3500					10,500	8,000	2,500
12				4000					6,500	6,500	0
13				2200					4,300	4,300	0
14								9000	13,300	8,000	5,300
15				1800					11,500	8,000	3,500
16				9000					2,500	2,500	0
17								5000	7,500	7,500	0
18				1700					5,800	5,800	0
Sheet2

Cell Formulas
Range	Formula
D5, H5	D5	=SUM(D10:D85)
E5:G5	E5	=SUM(E9:E85)
D6	D6	=D5*2.18
E6	E6	=E5*25
F6	F6	=F5*15
G6	G6	=G5*100
I6	I6	=D6+E6+F6+G6
J9	J9	=IF(I9>8000,8000)
I10:I18	I10	=I9-D10+H10
J10:J18	J10	=IF(OR(D10>0,H10>0),MIN(I10,8000),0)
K10:K18	K10	=IF(OR(D10>0,H10>0),I10-J10,0)

Check the I10, J10, and K10 formulas. These also handle your latest requests.