VB Code or not to meet several requirement maybe Message Boxes

Status
Not open for further replies.

RLPeloquin

Board Regular
Joined
Jul 4, 2020
Messages
61
Office Version
  1. 2019
Platform
  1. Windows
I've backed myself in a corner with this spreadsheet. The formulas in cells C10 - N10. These cells automatically pick up previous "Meter Readings" which works great. But... the Initial "Meter Reading" can fall on different months. So... for example if the Initial "Meter Reading" say falls in "April" how would I put that reading in Cell "F10" without affecting the other formulas in C10 - N10. I'm thinking a VBA code that would Unprotect Sheet, Recognize the "Initial Start Month" maybe by a "Message Box" that a "Month" could be entered into and another "Message Box" for the "Initial Meter Reading" entered into, then put "Initial Meter Reading in Cell "F10". "Then Protect Sheet once entered. Any help greatly appreciated!
Generic Electricity Usage.xlsx
ABCDEFGHIJKLMN
1Site 1Your Company Name
2Name:Your Address
3Street Address:Your City, State & Zip Code
4City:State Abbr:Zip code:
5Phone:
6Email:Date of Meter Reading
7
8JanuaryFebruary MarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
9METER READING
10PREVIOUS MONTH            
11KWH USED            
12RATE0.12
13YOUR ENERGY USEAGE FOR:
14JanuaryFebruary MarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
15KWH            
16TOTAL            
17AMOUNT PAID
18BALANCE DUE            
19
20DEPOSIT (IF ANY) Note about Deposit:
21Method of Payment Amount Paid Amount Due
22January January 
23February February 
24March March 
25April April 
26May May 
27June June 
28July July 
29August August 
30September September 
31October October 
32November November 
33December December 
34
35
36Total Payments Total Charges 
37
38YOU HAVE A CREDIT OF: 
Site 1
Cell Formulas
RangeFormula
C10C10=IF(N10="","",N9)
D10:N10D10=IF(D9="","",C9)
C11:N11C11=IF(C9="","",C9-C10)
C15:N15C15=IF(C9="","",C11)
C16C16=IF(C15="","",C15*B12)
D16D16=IF(D15="","",D15*B12)
E16E16=IF(E15="","",E15*B12)
F16F16=IF(F15="","",F15*B12)
G16G16=IF(G15="","",G15*B12)
H16H16=IF(H15="","",H15*B12)
I16I16=IF(I15="","",I15*B12)
J16J16=IF(J15="","",J15*B12)
K16K16=IF(K15="","",K15*B12)
L16L16=IF(L15="","",L15*B12)
M16M16=IF(M15="","",M15*B12)
N16N16=IF(N15="","",N15*B12)
C18:N18C18=IF(C15="","",C16-C17)
E22E22=C17
E23E23=D17
E24E24=E17
E25E25=F17
E26E26=G17
E27E27=H17
E28E28=I17
E29E29=J17
E30E30=K17
E31E31=L17
E32E32=M17
E33E33=N17
I22I22=C18
I23I23=D18
I24I24=E18
I25I25=F18
I26I26=G18
I27I27=H18
I28I28=I18
I29I29=J18
I30I30=K18
I31I31=L18
I32I32=M18
I33I33=N18
E36E36=SUM(E22:E33)+C20
I36I36=SUM(C16:N16)
D38D38=IF(I36>E36,"YOU OWE:","YOU HAVE A CREDIT OF:")
G38G38=E36-I36
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This appears to be a duplicate of this. Please don't post the same question more than once.
 
Status
Not open for further replies.

Forum statistics

Threads
1,136,274
Messages
5,674,770
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top