On Tue, 28 Jun 2005 20:47:35 -0500, qflyer
wrote:
Well, here is a very simple UDF written in VBA that should get you
started.
It assumes your Dates are in Column A and your approaches are in Column
B.
It will display your current currency date, regardless of whether it is
before
or after TODAY. It can be extensively modified depending on your
requirements
and needs.
To use it, enter
=instcur()
in any cell.
To enter it, <alt-F11 opens the VB Editor. Ensure your project is
highlighted
in the project explorer window, then Insert/Module and paste the code
below
into the window that opens:
==================
Option Explicit
Function InstCur() As Date
Application.Volatile
Dim i As Long
Dim LastRow As Long
Dim AppCount As Long
Const ReqAppCount As Long = 6
LastRow = Range("A65535").End(xlUp).Row
For i = LastRow To 1 Step -1
AppCount = AppCount + Cells(i, 2).Value
If AppCount = ReqAppCount Then Exit For
Next i
InstCur = Cells(i, 1) + 180
End Function
==================
--ron
Ron,
I created a simple workbook with just a few dates in A and a few
approaches in B, and this works perfectly.
However, being the novice that I am, I don't understand how to change it
from looking in column B for the approaches to looking at column F where
I enter my approaches.
The Cells property has row number and column number as its arguments. So for
Column F, you would change the column argument from 2 to 6:
AppCount = AppCount + Cells(i, 6).Value
^
Also, I don't know if this matters, but my individual logbook entries
start in row 50. The rows above 50 are used for a summary of flight
time, etc...
It shouldn't make much difference since, at this point in your career, the loop
will end before it gets back to 50. but for good practice, you could change
the loop entry to read:
For i = LastRow To 50 Step -1
^^
One final request...would you be able to post the code you use for your
logbook that uses the 6 calendar months rather than 180 days? That could
be useful to me in the future if I ever move to a different company
without a 180 day requirement.
I do things much differently in my own log book. Right now I keep track of a
number of data points, so when I analyze, I step forward through every record
(row) in the logbook and accumulate the data for which I am looking. I make
use of multidimensional arrays for this.
In addition, it is set up so I can add columns and other data that I might want
to summarize or change without extensive rewriting.
Some of the data I summarize is for legal purposes; some for the insurance
company; and some for personal purposes.
The worksheet itself is set up with column headings in row 1. The headings in
which I am interested are listed in the Select Case statement; although there
are other columns besides those.
The headings of my analysis summary are stored in Msg(0,0) through Msg(10,0)
and you can see where I set that up at the beginning.
If you want to mimic it, set up your sheet with column names in Row 1; and have
at least some of them match what I'm using.
This is certainly not ready for distribution; it was written for my personal
use so doesn't have to be too fancy. In addition, I use another routine for
data entry, and because of that restriction, I don't have any sanity checks on
the data. Those are done, to some extent, at the time of data entry.
To check for the expiration date of IFR currency; I set up a two dimensional
array Months(0 to 6,0). The first element contains the first day of the month
for each month going back six calendar months from TODAY. So if I ran it
today, the array would look like:
?months(0,0)
6/1/2005
?months(1,0)
5/1/2005
?months(2,0)
4/1/2005
..
..
..
?months(6,0)
12/1/2004
As I am analyzing each record, if there is an IFR app logged on that record,
that number (number of approaches) gets added to the appropriate bin in Months.
So when I'm done, I have 7 monthly bins with the number of IFR apps I did in
each month.
So then I step backwards through each month until I get to six (6). If I don't
get to six, then I output "Not Current".
If I do get to six, then I add seven months and subtract one day to the month
date of the bin in which I got to six.
Good luck! There's probably some word-wrap problems in the Sub, but the
VB
module should catch it.
-------------------------
Row 1 of Worksheet: Note that IFR Apps and Gnd Trnr are the only header
columns with spaces.
Date Make/Model N# From To
Purpose Notes ASEL ASES AMEL XC
Day Night IFR Hood IFR Apps
Gnd Trnr Dual SIC PIC Total
===============================
Sub Analyze()
Dim LastRow As Long, LastColumn As Long
Dim c As Range
Dim i As Integer, j As Integer, k As Integer
Dim Fmt1 As String, Fmt2 As String, Fmt3 As String
Dim Response As Integer
Dim Days90 As Date, Months(6, 1) As Variant
Dim Msg(10, 2) As Variant, ColHeads() As String, title As String, Prompt As
String
Dim TotalHours As Double, TotalXC As Double, TotalIFR As Double
Dim Last90 As Double, IFRApps As Integer
Dim Years1 As Date
LastRow = ActiveSheet.Range("a65536").End(xlUp).Row
LastColumn = ActiveSheet.Range("iv1").End(xlToLeft).Column
Days90 = DateAdd("d", -90, Date)
Years1 = DateAdd("yyyy", -1, Date)
For i = 0 To 6
Months(i, 0) = DateSerial(Year(Date), Month(Date) - i, 1)
Months(i, 1) = 0
Next i
Fmt1 = "#,##0.0"
Fmt2 = "0"
Fmt3 = "m/d/yyyy"
ReDim ColHeads(LastColumn)
Msg(0, 0) = "Total Hours as PIC: "
Msg(0, 2) = Fmt1
Msg(1, 0) = "Total Cross-Country Hours: "
Msg(1, 2) = Fmt1
Msg(2, 0) = "Total IFR Hours: "
Msg(2, 2) = Fmt1
Msg(3, 0) = "PIC Hours Last 90 days: "
Msg(3, 2) = Fmt1
Msg(4, 0) = "IFR Approaches in Last 6 months: "
Msg(4, 2) = Fmt2
Msg(5, 0) = "IFR Current until: "
Msg(5, 2) = Fmt3
Msg(6, 0) = "Total ASEL hours: "
Msg(6, 2) = Fmt1
Msg(7, 0) = "Total Day hours: "
Msg(7, 2) = Fmt1
Msg(8, 0) = "Total Night hours: "
Msg(8, 2) = Fmt1
Msg(9, 0) = "PIC Hours Since " & Format(Years1, "m/d/yy") & ": "
Msg(9, 2) = Fmt1
Msg(10, 0) = "Total Time: "
Msg(10, 2) = Fmt1
For i = 1 To LastColumn
ColHeads(i - 1) = Cells(1, i)
Next i
title = "Currency"
For i = 2 To LastRow
For j = 0 To LastColumn - 1
Select Case ColHeads(j)
Case Is = "PIC"
Msg(0, 1) = Msg(0, 1) + Cells(i, j + 1)
If Cells(i, 1) Days90 Then
Msg(3, 1) = Msg(3, 1) + Cells(i, j + 1)
End If
If Cells(i, 1) Years1 Then
Msg(9, 1) = Msg(9, 1) + Cells(i, j + 1)
End If
Case Is = "IFR"
Msg(2, 1) = Msg(2, 1) + Cells(i, j + 1)
Case Is = "XC"
Msg(1, 1) = Msg(1, 1) + Cells(i, j + 1)
Case Is = "IFR Apps"
For k = 0 To 6
If Cells(i, 1) Months(k, 0) Then
Months(k, 1) = Months(k, 1) + Cells(i, j + 1)
End If
Next k
Case Is = "ASEL"
Msg(6, 1) = Msg(6, 1) + Cells(i, j + 1)
Case Is = "Day"
Msg(7, 1) = Msg(7, 1) + Cells(i, j + 1)
Case Is = "Night"
Msg(8, 1) = Msg(8, 1) + Cells(i, j + 1)
Case Is = "Total"
Msg(10, 1) = Msg(10, 1) + Cells(i, j + 1)
End Select
Next j
Next i
Msg(4, 1) = Months(6, 1) 'IFR Approaches
For j = 0 To 6
If Months(j, 1) = 6 Then
Exit For
End If
Next j
If j = 7 Then
Msg(5, 1) = "!NOT CURRENT!"
Else
Msg(5, 1) = DateAdd("m", 7, Months(j, 0)) - 1
End If
For i = 0 To UBound(Msg)
Prompt = Prompt & Msg(i, 0) & Format(Msg(i, 1), Msg(i, 2)) & Chr(10)
Next i
Response = MsgBox(Prompt, vbOKOnly, title)
End Sub
========================
--ron