MrExcel.com - Your One Stop for Excel Tips & Solutions

Date question, and formatting question...

Posted by Jim McQueen on January 05, 2002 6:46 AM

Hi folks, here is my stuff for you to see, so far. My two questions will follow at the end okay? Thanks! Thanks again Joe Was...
=======================

Function Logs()

With Worksheets("Sheet1")
.Range("B1").Value = "Tower #1"
.Range("B2").Value = "Make-up"
.Range("B1:B2").Font.Bold = True
.Range("B1:B2").HorizontalAlignment = xlCenter
.Range("B3").Value = "250"
.Range("C1").Value = "Total"
.Range("C2").Value = "Gallons"
.Range("C3").Value = "Used"
.Range("C1:C3").Font.Bold = True
.Range("C1:C3").HorizontalAlignment = xlCenter
.Range("D1").Value = "Tower #1"
.Range("D2").Value = "Blow Down"
.Range("D1:D2").Font.Bold = True
.Range("D1:D2").HorizontalAlignment = xlCenter
.Range("D3").Value = "250"
.Range("E1").Value = "Total"
.Range("E2").Value = "Gallons"
.Range("E3").Value = "Used"
.Range("E1:E3").Font.Bold = True
.Range("E1:E3").HorizontalAlignment = xlCenter
.Range("F1").Value = "Tower #2"
.Range("F2").Value = "Make-up"
.Range("F1:F2").Font.Bold = True
.Range("F1:F2").HorizontalAlignment = xlCenter
.Range("F3").Value = "250"
.Range("G1").Value = "Total"
.Range("G2").Value = "Gallons"
.Range("G3").Value = "Used"
.Range("G1:G3").Font.Bold = True
.Range("G1:G3").HorizontalAlignment = xlCenter
.Range("H1").Value = "Tower #2"
.Range("H2").Value = "Blow Down"
.Range("H1:H2").Font.Bold = True
.Range("H1:H2").HorizontalAlignment = xlCenter
.Range("H3").Value = "250"
.Range("I1").Value = "Total"
.Range("I2").Value = "Gallons"
.Range("I3").Value = "Used"
.Range("I1:I3").Font.Bold = True
.Range("I1:I3").HorizontalAlignment = xlCenter
.Range("B3").Font.ColorIndex = 11
.Range("D3").Font.ColorIndex = 11
.Range("F3").Font.ColorIndex = 11
.Range("H3").Font.ColorIndex = 11
.Range("A1").Select
End With
Sheets("Sheet1").Select
Range("D1").Select
End Function

Sub Utilities()
'By: Joe Was, 12/2001.
'This is the application.

Dim clickTest As Variant
Dim Tower1, Makeup As Variant
Dim Tower1BlowDown As Variant
Dim Tower2Makeup As Variant
Dim Tower2BlowDown As Variant
Dim Tower1MakeupD As Variant
Dim Tower1BlowDownD As Variant
Dim Tower2MakeupD As Variant
Dim Tower2BlowDownD As Variant
Dim Tower1MakeupO As Variant
Dim Tower1BlowDowno As Variant
Dim Tower2MakeupO As Variant
Dim Tower2BlowDowno As Variant
Dim TodaysDate As Date

'Get new reading.
Tower1Makeup = Application.InputBox(prompt:="What is the new reading for the Tower1Makeup?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)

'Test for errors.
If Tower1Makeup = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop

'Do data updating and math.
Application.ScreenUpdating = True
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("B65536").End(xlUp).Offset(1, 0).Value = Tower1Makeup
Tower1MakeupO = Worksheets("Sheet1").Range("B65536").End(xlUp).Offset(-1, 0).Value
Tower1MakeupD = Tower1Makeup - Tower1MakeupO
Worksheets("Sheet1").Range("C65536").End(xlUp).Offset(1, 0).Value = Tower1MakeupD
Application.ScreenUpdating = True

'The next two check boxes are designed as above!
Worksheets("Sheet1").Activate
Tower1BlowDown = Application.InputBox(prompt:="What is the new reading for the Tower1BlowDown?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If Tower1BlowDown = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = True
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("D65536").End(xlUp).Offset(1, 0).Value = Tower1BlowDown
Tower1BlowDowno = Worksheets("Sheet1").Range("D65536").End(xlUp).Offset(-1, 0).Value
Tower1BlowDownD = Tower1BlowDown - Tower1BlowDowno
Worksheets("Sheet1").Range("E65536").End(xlUp).Offset(1, 0).Value = Tower1BlowDownD
Application.ScreenUpdating = True

Tower2Makeup = Application.InputBox(prompt:="What is the new reading for the Tower2Makeup?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If Tower2Makeup = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = True
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("F65536").End(xlUp).Offset(1, 0).Value = Tower2Makeup
Tower2MakeupO = Worksheets("Sheet1").Range("F65536").End(xlUp).Offset(-1, 0).Value
Tower2MakeupD = Tower2Makeup - Tower2MakeupO
Worksheets("Sheet1").Range("G65536").End(xlUp).Offset(1, 0).Value = Tower2MakeupD
Application.ScreenUpdating = True

Tower2BlowDown = Application.InputBox(prompt:="What is the new reading for the Tower2BlowDown?" & Chr(13) & Chr(13) & _
"If not reporting a reading, enter: 0 or press Cancel!", Title:="Enter Utility Reading:", Default:="0", Type:=1)
If Tower2BlowDown = 0 Then GoTo myStop
If Cancel = True Then GoTo myStop
Application.ScreenUpdating = True
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("H65536").End(xlUp).Offset(1, 0).Value = Tower2BlowDown
Tower2BlowDowno = Worksheets("Sheet1").Range("H65536").End(xlUp).Offset(-1, 0).Value
Tower2BlowDownD = Tower2BlowDown - Tower2BlowDowno
Worksheets("Sheet1").Range("I65536").End(xlUp).Offset(1, 0).Value = Tower2BlowDownD
Application.ScreenUpdating = True

'Test for errors and other events.
End
End
myStop:
MsgBox prompt:="Operator ended update, no action taken!", Title:="UpDate Stopped!"
End
Emp:
MsgBox prompt:="You did not check an Utility box.", Title:="Input Data Error!"
End
myEnd:
End Function

============

Above is the stuff for Page #1. I am going to add the stuff for page #2 and #3 next. I realize that the next two pages will be essentially the same basic componants of the above. So;

#1 Do I add the componants in with those of page one, in series or clusters? Or do I put each page as a stand alone group after the preceeding page's group? I sure hope this isn't a stupid question. :-)

Question #2 I have reserved column "A" on all three sheets as a place to put the date. Each night when I update the sheets, I want the date to automatically enter itsef on that line in column "A". Or if I don't get to update the sheets for a couple of nights, and I enter two or three days worth of data at once. The date for each day would still enter correctly on each line as I go.

Thanks folks! I am brand new to Excel. This is the first time I have even looked at it. So I am really really green. But I like this program and I'm smitten. :-)

Jim

Posted by Bariloche on January 05, 2002 8:23 AM

Jim,

Looks like you're taking to this like the proverbial duck to water. Congrats.

I haven't gone through all your code, nor will I probably have the time, but I want to mention a few things regarding technique that I noticed as I scanned through it.

1) Whenever you look through your code and notice that you're repeating stuff over and over you want to stop and think of how you can do that thing once, if possible. I'm referring right now to the first part of your code where you're setting up the headers (I believe). The code where you make the font bold and align horizontally could be done across all the affected cells at one time (with two lines of code) rather than for each column grouping. It may seem like a nit, but these little efficiencies can add up.

2) You use GoTo to terminate execution of your code. Its a "generally accepted practice," (at least in my opinion) that GoTo should only be used for error handling except in the most extreme of cases. Using GoTo to transfer execution of your code leads to what is termed "spaghetti code." So called because then your code becomes as easy to follow as trying to follow a single spaghetti noodle on a plate of pasta.

So for your code such as: If Tower1Makeup = 0 Then GoTo myStop

I would recode it like:

If Tower1Makeup = 0 then
MsgBox prompt:="Operator ended update, no action taken!", Title:="UpDate Stopped!"
Exit Sub
End If

Like I said I haven't really gone through your code in detail, but I think that I've answered your first question. In other words, if you're asking, "Should I copy this code and make the appropriate changes for my other pages" I'd say "No." The first thing you should look at is how to use your existing code multiple times. by looping or whatever.

For question 2 I'd have you search through the recent posts on the board. This topic has been covered in a number of other posts. If that search yields nothing then re-post your question. However, I would say that I don't know how you are going to get Excel to recognize that a particular line of data belongs to a particular day without you telling it. I'm referring to the part of your question about not entering data for a few days but still getting the correct day entered in your log.

All in all though you've done alot of good work. Keep it up.


have fun