ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Creating intermediate date values (https://www.excelbanter.com/excel-programming/388353-creating-intermediate-date-values.html)

Jim Berglund[_2_]
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

Jim Berglund[_2_]
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

joel
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

Jim Berglund[_2_]
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

joel
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

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



joel
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




Jim Berglund[_2_]
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

Jim Berglund[_2_]
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

Norman Jones
Creating intermediate date values - reformatted
 
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

Jim Berglund[_2_]
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

Barb Reinhardt
Creating intermediate date values - reformatted
 
Jim, what is the value for NewDate, OldDate, RowCount and OldRow when it has
an error.

I tried it with minimal data and had text in NewDate.


"Jim Berglund" wrote:

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

Jim Berglund[_2_]
Creating intermediate date values - reformatted
 
I just ran it with the following (sufficient data for the test):
Cells (2,12) = 10/15/2004
Cells (23,12) = 4/23/2007
Cells(24,12)= 10/15/1996
Cells(64,12)=10/15/2004

New Date is ""
OldDate is 10/15/1996
RowCount is 24
OldRow is 23
DeltaDate is -3842

I think it dies on row 23.

Jim

"Barb Reinhardt" wrote in message
...
Jim, what is the value for NewDate, OldDate, RowCount and OldRow when it
has
an error.

I tried it with minimal data and had text in NewDate.


"Jim Berglund" wrote:

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

Jim Berglund[_2_]
Creating intermediate date values - reformatted
 
Oops! I used column L instead of G (I had changed my copy of the code) ,
Change the Cells values from 12 to 7 in the data below

Jim
"Jim Berglund" wrote in message
news:pcaZh.147500$aG1.120271@pd7urf3no...
I just ran it with the following (sufficient data for the test):
Cells (2,12) = 10/15/2004
Cells (23,12) = 4/23/2007
Cells(24,12)= 10/15/1996
Cells(64,12)=10/15/2004

New Date is ""
OldDate is 10/15/1996
RowCount is 24
OldRow is 23
DeltaDate is -3842

I think it dies on row 23.

Jim

"Barb Reinhardt" wrote in
message ...
Jim, what is the value for NewDate, OldDate, RowCount and OldRow when it
has
an error.

I tried it with minimal data and had text in NewDate.


"Jim Berglund" wrote:

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


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com