Small bug with Macro

[Jinky Julie]

Hello everybody... New to VBA.. getting by but need some help every so often....

Someone was nice to help me with a macro.. and gave me this:


Dim oTable As Table
Dim orng As Range
Dim iMin As Long
Dim iCol As Integer
Set oTable = ActiveDocument.Tables(1)
iCol = 2 'Column with the times
With oTable
Set orng = .Cell(2, iCol).Range
orng.End = orng.End - 1
sText = orng.Text
For i = 3 To .Rows.Count
Set orng = .Cell(i, iCol).Range
orng.End = orng.End - 1
iMin = Minute(orng.Text) - Minute(sText)
If iMin >= 10 Then
.Rows(i).Shading.BackgroundPatternColor = _
wdColorLightBlue
.Rows(i - 1).Shading.BackgroundPatternColor = _
wdColorLightBlue
End If
sText = orng.Text
Next i
For i = .Rows.Count To 2 Step -1
If .Rows(i).Shading.BackgroundPatternColor _
<> wdColorLightBlue Then
.Rows(i).Delete
End If
Next i

End With


Works great... but... The calculation here is only dealing with the Minutes portion of the time in each cell... therefore will only highlight cells where there is an actual difference of 10 (the number) NOT 10 minutes in elapsed time... I would really like it to work...

I know Word is not meant for this type of thing... but I have no choice... cannot use Excel...

Any help would be great!!!

Julie

[jfournier]

I think you can replace:

iMin = Minute(orng.Text) - Minute(sText)
If iMin >= 10 Then

with:

if cdate(orng.Text) - cdate(sText) >= timeserial(0,10,0) then

All it's doing is converting each string to a date, finding the difference, and comparing that to a date that is 10 minutes...I think that should work...

[fumei]

Yes, you need to deal with time, not numbers.

You can use TimeSerial, as jfournier suggests, or possibly DateDiff. If you use DateDiff, you will need to understand and use Format. It seems the values in the cells are text, not dates. Which is why you are not getting date/time values to work correctly. CDate (as jfournier posted) will work.

How are you getting the values in the cells in the first place?

[Jinky Julie]

Hi to both.. and thanks...

I am sorry if I didn't explain everything fully....

It's a table (varying lengths (rows)) of time entries (and other info).. I wish to highlight any subsequent pairs of rows where the elapsed time between the two is 10 minutes or more... and then delete anything that is not highlighted... the problem is where (examples) 07:59 and 08:10 will not highlight but 12:30 and 12:53 will...

Again I realize that Word is just not built for time like Excel is BUT... Word is what I got...

Thanks again guys....

[Jinky Julie]

Hi guys... I kept researching....

Came across this....

iMin = (Hour(orng.Text) * 60 + Minute(orng.Text)) - _
(Hour(sText) * 60 + Minute(sText))

It seems to work......

[Jinky Julie]

Yikes!!!

One more thing... Sorry :banghead: ....

Besides deleting the non-highlighted cells... some rows may be in bold... I don't want those deleted... I thought this might work...

For i = .Rows.Count To 2 Step -1
If .Rows(i).Shading.BackgroundPatternColor _
<> wdColorGray10 Or Not .Cell(i).Font.Bold Then
.Rows(i).Delete
End If
Next i

But no such luck... I get a compile error .... Argument not optional...

Thanks.... for your help and patience... I will keep trying....

Julie

[fumei]

1. depending on whether you used a proper With statement, your OR parameters are not fully qualified.

.Cell(i) would be a child object of Range of the table.
.Rows(i) looks like a child object of the table.

Two very different things. Can you post more code?

2. "of time entries " Well. again, YOU may think of them as time entries, but if they are just text, then Word sees them as...text.

[Jinky Julie]

Hi folks... Here's the code as it stands with the part I tried to add regarding the bold text... Except for that, the code does what I want....

Compares times in subsequent rows
Highlights rows with elapsed time equal to or more than 10 minutes
Deletes rows not highlightedNow I forgot about the Bold rows, which I want to keep...



Dim oTable As Table
Dim orng As Range
Dim iMin As Long
Dim iCol As Integer
Set oTable = ActiveDocument.Tables(1)
iCol = 1
With oTable
Set orng = .Cell(2, iCol).Range
orng.End = orng.End - 1
sText = orng.Text
For i = 3 To .Rows.Count
Set orng = .Cell(i, iCol).Range
orng.End = orng.End - 1

iMin = (Hour(orng.Text) * 60 + Minute(orng.Text)) - _
(Hour(sText) * 60 + Minute(sText))

If iMin >= 10 Then
.Rows(i).Shading.BackgroundPatternColor = _
wdColorGray10
.Rows(i - 1).Shading.BackgroundPatternColor = _
wdColorGray10
End If

sText = orng.Text
Next i
For i = .Rows.Count To 2 Step -1
If .Rows(i).Shading.BackgroundPatternColor _
<> wdColorGray10 Or Not .Cell(i).Font.Bold Then
.Rows(i).Delete
End If
Next i

End With



J

[fumei]

your .Cell(i) is not fully qualified. It should fail.