Hi all,


I have long list of measurement timings with an interval of 10 minutes.

Like this:

time measure
1-1-2008 0:00 120
1-1-2008 0:10 117
1-1-2008 0:20 118

and so on for a whole month.

Sometimes however, there's a gap in time greater than 10 minutes.
I need to fill that gap with the missing time and also add NA for the
measure column. So I wrote the following rather straightforward code:

Sub fillmissing()
' create a gap of 10 minutes
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If jump = dfference Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"

End If
Loop
End Sub

But, somehow Excel finds 10 minutes unequal to 1/24/6
So, when i run the code i end up with a long list of new gaps of 10
minutes and a NA value in the second column.

I can't put my finger on it.

Does anybody have any suggestions?

Thanks in advance,


Sybolt

Hi Sybolt,
You have run into IEEE round-off error - see below
Replace the branch part of your code by:

If Abs(jump - difference) 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If


Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precision
http://support.microsoft.com/default...NoWebContent=1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"imda14u" wrote in message
...
Hi all,


I have long list of measurement timings with an interval of 10 minutes.

Like this:

time measure
1-1-2008 0:00 120
1-1-2008 0:10 117
1-1-2008 0:20 118

and so on for a whole month.

Sometimes however, there's a gap in time greater than 10 minutes.
I need to fill that gap with the missing time and also add NA for the
measure column. So I wrote the following rather straightforward code:

Sub fillmissing()
' create a gap of 10 minutes
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If jump = dfference Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"

End If
Loop
End Sub

But, somehow Excel finds 10 minutes unequal to 1/24/6
So, when i run the code i end up with a long list of new gaps of 10
minutes and a NA value in the second column.

I can't put my finger on it.

Does anybody have any suggestions?

Thanks in advance,


Sybolt

Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different story

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"imda14u" wrote in message
...
Hi all,


I have long list of measurement timings with an interval of 10 minutes.

Like this:

time measure
1-1-2008 0:00 120
1-1-2008 0:10 117
1-1-2008 0:20 118

and so on for a whole month.

Sometimes however, there's a gap in time greater than 10 minutes.
I need to fill that gap with the missing time and also add NA for the
measure column. So I wrote the following rather straightforward code:

Sub fillmissing()
' create a gap of 10 minutes
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If jump = dfference Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"

End If
Loop
End Sub

But, somehow Excel finds 10 minutes unequal to 1/24/6
So, when i run the code i end up with a long list of new gaps of 10
minutes and a NA value in the second column.

I can't put my finger on it.

Does anybody have any suggestions?

Thanks in advance,


Sybolt

Bernard Liengme schreef:

Replace the branch part of your code by:

If Abs(jump - difference) 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If


Thanks for the quick reply,

I tried your code but it runs only 1 time correctly.
for the next rows the code just adds the extra row and the NA

just as my code did.

Maybe I haven't put the code correctly it now looks
like this:

Sub fillmissing()
difference = 1 / 24 / 6

Range("a2").Select
Do Until ActiveCell.Text = ""
jump = ActiveCell.Offset(1, 0).Value - ActiveCell.Value
If Abs(jump - difference) 0.000000000001 Then
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 / 24 / 6
ActiveCell.Offset(0, 1).Formula = "=NA()"
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub



Any suggestions?

Sybolt

Niek Otten schreef:
Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different story


Niek,

thanks fot the reply,

I know about the option explicit statement, but sometimes it doesn't
seem worth the trouble.

Tell me about the different story.


:)

Sybolt

Niek cited your own routine as a perfect example of why you should always
use Option Explicit

In case you still haven't seen it, you have

"difference" and "dfference"

(I haven't looked to see if there are other errors too)

Regards,
Peter T

"imda14u" wrote in message
...
Niek Otten schreef:
Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different
story


Niek,

thanks fot the reply,

I know about the option explicit statement, but sometimes it doesn't seem
worth the trouble.

Tell me about the different story.


:)

Sybolt

You should'nt have told him what the typo was, Peter :-}

<sometimes it doesn't seem worth the trouble

OK. You had a problem. You probably (hopefully) spent some effort trying to
solve it.
You took the trouble to post a message. Several people (no idea how many)
tried to reconstruct your problem.
Some answered.

Now what do you think about <sometimes it doesn't seem worth the trouble

It's ALWAYS worth the trouble. BTW, What trouble?

I think there's no excuse for not using Option Explicit.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Peter T" <peter_t@discussions wrote in message
...
Niek cited your own routine as a perfect example of why you should always
use Option Explicit

In case you still haven't seen it, you have

"difference" and "dfference"

(I haven't looked to see if there are other errors too)

Regards,
Peter T

"imda14u" wrote in message
...
Niek Otten schreef:
Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different
story


Niek,

thanks fot the reply,

I know about the option explicit statement, but sometimes it doesn't seem
worth the trouble.

Tell me about the different story.


:)

Sybolt

<BTW, What trouble?

Once only:

ToolsOptionsClick "Require Variable declaration".

It will insert the Option Explicit line autoamtically

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message
...
You should'nt have told him what the typo was, Peter :-}

<sometimes it doesn't seem worth the trouble

OK. You had a problem. You probably (hopefully) spent some effort trying
to solve it.
You took the trouble to post a message. Several people (no idea how many)
tried to reconstruct your problem.
Some answered.

Now what do you think about <sometimes it doesn't seem worth the trouble

It's ALWAYS worth the trouble. BTW, What trouble?

I think there's no excuse for not using Option Explicit.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Peter T" <peter_t@discussions wrote in message
...
Niek cited your own routine as a perfect example of why you should always
use Option Explicit

In case you still haven't seen it, you have

"difference" and "dfference"

(I haven't looked to see if there are other errors too)

Regards,
Peter T

"imda14u" wrote in message
...
Niek Otten schreef:
Hi Sybolt

Always use Option Explicit

Now you allowed yourself a typo without VBE detecting it

If you correct it, you'll find another error, but that's a different
story


Niek,

thanks fot the reply,

I know about the option explicit statement, but sometimes it doesn't
seem worth the trouble.

Tell me about the different story.


:)

Sybolt