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
EQUIPMENT# B
R_TYPE C READING D UOM E
NOTES F G
New Dates H
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 ENHR 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

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

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
>
>
>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:5FB4E715-FCD5-4D23-B831-(E-Mail Removed)...
> 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
>>
>>
>>
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:5FB4E715-FCD5-4D23-B831-(E-Mail Removed)...
> > 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
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>

Hi Jim,

'----------------
I have some questions:
1. You didn't declare any variables. When can you get away with this?
'----------------

IMHO, all variables should always be declared explicitly.

In this connection. you might wish to see Chip Pearson at:

Using Variables (Properly) In VBA
http://www.cpearson.com/excel/variables.htm

Unlike Joel, I have never experienced problems as the
result of the declaration of variables. I should be intrigued
to see an example of an error caused by explict declaration.

'---------------
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...
'----------------

The ColumnIndex argument can be either a string or a number:
see VBA help for the Item property of a range object: see VBA
help on the Item property of a range object.


---
Regards,
Norman

Norm: the 2 areas I had problems with declarations of variable is Reading
worksheet cells and passing variables between subroutines and between
functions.

When you are reading cells that have number and strings a you try to read
these values into a variable you will get errors if youdeclare the variable a
number , string or even variant. I also have had problems passing arrrays
between functions and trying to declare the variable type of the array.

I just got tired of having to fight excel with delcaring variables and then
wasting a lot of time finding if I simply removed the declaration it worked.
thje documentation of excel is poor and there just seems to be a lot of
annomolies that takes lots of experience and lots of time to figure out. I'm
just very tired of having excel waste my time like declaring a variable and
then find out later the declaration was causing the error.

Norm, some day you will eat your words and agree with me!

"Norman Jones" wrote:

> Hi Jim,
>
> '----------------
> I have some questions:
> 1. You didn't declare any variables. When can you get away with this?
> '----------------
>
> IMHO, all variables should always be declared explicitly.
>
> In this connection. you might wish to see Chip Pearson at:
>
> Using Variables (Properly) In VBA
> http://www.cpearson.com/excel/variables.htm
>
> Unlike Joel, I have never experienced problems as the
> result of the declaration of variables. I should be intrigued
> to see an example of an error caused by explict declaration.
>
> '---------------
> 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...
> '----------------
>
> The ColumnIndex argument can be either a string or a number:
> see VBA help for the Item property of a range object: see VBA
> help on the Item property of a range object.
>
>
> ---
> Regards,
> Norman
>
>
>

Thanks to both of you for your explanations. I appreciated the link to
Chip's website, and will take some time to read it.
Jim
"Norman Jones" <(E-Mail Removed)> wrote in message
news:%23N%(E-Mail Removed)...
> Hi Jim,
>
> '----------------
> I have some questions:
> 1. You didn't declare any variables. When can you get away with this?
> '----------------
>
> IMHO, all variables should always be declared explicitly.
>
> In this connection. you might wish to see Chip Pearson at:
>
> Using Variables (Properly) In VBA
> http://www.cpearson.com/excel/variables.htm
>
> Unlike Joel, I have never experienced problems as the
> result of the declaration of variables. I should be intrigued
> to see an example of an error caused by explict declaration.
>
> '---------------
> 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...
> '----------------
>
> The ColumnIndex argument can be either a string or a number:
> see VBA help for the Item property of a range object: see VBA
> help on the Item property of a range object.
>
>
> ---
> Regards,
> Norman
>

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" <(E-Mail Removed)> wrote in message
news:B62E6D08-DFD3-45BF-93A9-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:5FB4E715-FCD5-4D23-B831-(E-Mail Removed)...
>> > 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>>

Hi Joel,

'-------------------
Norm: the 2 areas I had problems with declarations of variable is Reading
worksheet cells and passing variables between subroutines and between
functions.

When you are reading cells that have number and strings a you try to read
these values into a variable you will get errors if youdeclare the variable
a
number , string or even variant. I also have had problems passing arrrays
between functions and trying to declare the variable type of the array.

I just got tired of having to fight excel with delcaring variables and then
wasting a lot of time finding if I simply removed the declaration it worked.
thje documentation of excel is poor and there just seems to be a lot of
annomolies that takes lots of experience and lots of time to figure out.
I'm
just very tired of having excel waste my time like declaring a variable and
then find out later the declaration was causing the error.

Norm, some day you will eat your words and agree with me!
'-------------------


Don't hold your breath! :-)


---
Regards,
Norman