Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Creating intermediate date values
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values
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




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values - reformatted
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





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Creating intermediate date values - reformatted
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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values - reformatted
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








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Creating intermediate date values - reformatted
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









Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values - reformatted
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











Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values - reformatted
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











Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Creating intermediate date values - reformatted
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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Creating intermediate date values - reformatted
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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Creating intermediate date values - reformatted
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" wrote in message
...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Intermediate Levels in Date Dimension Domenick Excel Discussion (Misc queries) 0 December 16th 09 10:09 PM
What is contained in excel intermediate? Maria Excel Discussion (Misc queries) 6 November 2nd 08 09:28 PM
Intermediate value of a set of array. HARSHAWARDHAN. S .SHASTRI[_2_] Excel Worksheet Functions 5 September 25th 08 09:01 PM
How to get intermediate values from smooth graph in Excel ? Tushar Charts and Charting in Excel 6 February 13th 06 08:39 PM
date returning time in vba intermediate window papa jonah Excel Programming 3 December 25th 05 02:28 AM


All times are GMT +1. The time now is 07:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement