Joel, I'm running into an error 13 Type mismatch on the following row, and
can't seem to fix it.
DeltaDate = (NewDate - OldDate) / _ (RowCount - OldRow)

My column has been formatted to date.

Can you help again, please.

Jim



"Joel" wrote in message
...
I'm an old c Programmmer that stronly believes in declaring every
variabble.
too many times I got screwed in VBA declaring variables and finding out my
code onloy worked when I removed the declarations. Microsoft products
stink
when it comes to followings structrues. then times when you have to
declare
variables vary. You definitely need to delarre when you are dealing with
arrays. You also need to use declarations when you really need large
number
such as long, and double. I also use declarations when IO want to make
surre when I read a cell it is coverted to a string or a number. VBA will
automatically convert a cell to the format that is declared in VBA.

Using byREf or byVal is need often when calling one subroutine from
anotrher
subroutine.

"Jim Berglund" wrote:

Wow! it worked perfectly! Thanks, Joel!

I have some questions:
1. You didn't declare any variables. When can you get away with this?
2. I've never seen the use of a Column name in a Cell variable. eg
Cells(RowCount - 1, "G"). It works, so I guess I really don't need to
know
why - but i've never seen it documented...

Thanks again, Joel

Jim Berglund


"Joel" wrote in message
...
Jim: See if this works. The code is a little tricky. Let me know if
you
agree with my calulations.

Sub Prorate()

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

First = True
For RowCount = 2 To LastRow
If IsEmpty(Cells(RowCount, "A")) Then
'if last row was empty use todays date to prorate
If IsEmpty(Cells(RowCount - 1, "G")) Then
'Use last NewDate to prorate
OldDate = NewDate
NewDate = Now()
DeltaDate = (NewDate - OldDate) / _
(RowCount - OldRow)
'fill in prorated dates
For RowCount2 = OldRow To (RowCount - 1)

MyDate = Cells(RowCount2 - 1, "G") + _
DeltaDate
Cells(RowCount2, "G") = MyDate
Next RowCount2

End If
First = True
Else
If First = True Then

OldDate = Cells(RowCount, "G")
OldRow = RowCount
First = False
Else
If Not IsEmpty(Cells(RowCount, "G")) Then
NewDate = Cells(RowCount, "G")
DeltaDate = (NewDate - OldDate) / _
(RowCount - OldRow)
'fill in prorated dates
For RowCount2 = (OldRow + 1) To (RowCount - 1)

MyDate = Cells(RowCount2 - 1, "G") + _
DeltaDate
Cells(RowCount2, "G") = MyDate
Next RowCount2
OldDate = NewDate
OldRow = RowCount
End If
End If
End If

Next RowCount

End Sub

"Jim Berglund" wrote:

I'm creating a demonstration database for Preventative Maintenance.

I hadcreated my PM program sequence, and established a set of
reasonable
dates and mileages between them.

Afterwards, I wanted to add some other programs, and did, but now I
need
to
re-balance the dates & mileages.

For example, I need to insert n date values between two current
dates, n
rows apart. Each date could be 1/6th of the total number of days
between
the
two current dates, but I'd prefer there to be some random variences.
A B C D E
F
G
EQPT# R_TYPE READING UOM NOTES ACT New Dates
Number
of
Blank Lines
AS145-B ENHR 10665 MLS PDI Y
9/12/2004
4:35
AS145-B ENHR 48773 MLS PMA Y
AS145-B ENHR 64941 MLS PMB Y
AS145-B ENHR 97337 MLS PMA Y
AS145-B ENHR 139082 MLS Misc Y
AS145-B ENHR 170228 MLS PMB Y
AS145-B ENHR 251218 MLS Misc Y
AS145-B ENHR 258492 MLS PMDOT Y 9/11/2005
11:39
6
AS145-B ENHR 258492 MLS Retorque Y
9/15/2005
3:40
AS145-B ENHR 340574 MLS PMA Y
AS145-B ENHR 342890 MLS PMC Y
AS145-B ENHR 375486 MLS PMA Y
AS145-B ENHR 379149 MLS PMB Y
AS145-B ENHR 392653 MLS PMA Y
AS145-B ENHR 497397 MLS PMDOT Y 9/10/2006
22:51
5
AS145-B E NHR 497397 MLS Retorque Y
9/13/2006
9:36
AS145-B ENHR 544029 MLS PMA Y
AS145-B ENHR 554863 MLS PMC Y
AS145-B ENHR 578999 MLS PMA Y
AS145-B ENHR 807683 MLS PMB Y
AS145-B ENHR 1222337 MLS PMA Y

AS224-A ENHR 193 MLS PDI Y
11/3/2000 3:38 5




As you can see for this equipment unit, AS145-B, the first distance
reading
of 10665 occurred on 9/12/2004.
A year later, a DOT inspection was done at 256492 miles, and there
were
6
readings between those two.
I want to prorate the dates of the other service actions.

Has anyone got an idea on how to start? I've tried, but quickly got
bogged
down trying to reference the date values I need to use to perform the
calculations on. How do you find the 'next non-blank value'?

Thanks
Jim Berglund