Hi,

Someone has kindly written a macro for me which calculates the longest time interval (the time, in hours, is recorded in column C) corresponding to the variable data in column V. The macro finds the time window (in column C) that corresponds to the CONSECUTIVE Max data values in column V. We have set the MAX level range to the "Max value in column V to Max-0.5".

So, for example, for the following data (which exists on 'Sheet1' in my Excel file):

ColC ColV

<tbody>

</tbody>

The macro generates the following output in cell D101 of 'Summary' sheet:

Level considered Max..... 2.5 to 3

Number of times the Level Hit... 1

Longest of Which Lasted for.... 2 h

Corresponding Time Window... 2 to 4 h

Obviously, the macro has failed to recognize values higher than 3, which are CONSECUTIVE and are within MAX plus 0.5! The CORRECT report should look like this:

Level considered Max.....

Number of times the Level Hit... 1

Longest of Which Lasted for....

Corresponding Time Window...

I guess, the problem may be arising due to the macro 'rounding off' the numbers??

I can work with complex Excel formulas, but I am not a macro writer yet So, I can't figure out how to fix it. I hope someone will be able to correct the macro for me, please. Thanks a lot.

Here is the macro:

Someone has kindly written a macro for me which calculates the longest time interval (the time, in hours, is recorded in column C) corresponding to the variable data in column V. The macro finds the time window (in column C) that corresponds to the CONSECUTIVE Max data values in column V. We have set the MAX level range to the "Max value in column V to Max-0.5".

So, for example, for the following data (which exists on 'Sheet1' in my Excel file):

ColC ColV

0 | 1.27 |

0.0 | 1.42 |

2.0 | 2.90 |

4.0 | 3.00 |

6.0 | 3.20 |

8.0 | 1.74 |

<tbody>

</tbody>

The macro generates the following output in cell D101 of 'Summary' sheet:

Level considered Max..... 2.5 to 3

Number of times the Level Hit... 1

Longest of Which Lasted for.... 2 h

Corresponding Time Window... 2 to 4 h

Obviously, the macro has failed to recognize values higher than 3, which are CONSECUTIVE and are within MAX plus 0.5! The CORRECT report should look like this:

Level considered Max.....

**2.7 to 3.2**Number of times the Level Hit... 1

Longest of Which Lasted for....

**4 h**Corresponding Time Window...

**2 to 6 h**I guess, the problem may be arising due to the macro 'rounding off' the numbers??

I can work with complex Excel formulas, but I am not a macro writer yet So, I can't figure out how to fix it. I hope someone will be able to correct the macro for me, please. Thanks a lot.

Here is the macro:

Code:

```
Sub MaxABEduration()
Sheets("Sheet1").Select
Dim Rng As Range, Dn As Range, nRng As Range, oMax As Long, R As Range
Dim MyMax As Long, CDif As Integer
Dim col As Integer
col = 3
If col = 0 Then Exit Sub
CDif = col - 22
Set Rng = Range("V5").Resize(50) '50 being the number of rows I want to look at
MyMax = Application.Max(Rng)
Sheets("summary").Select
For Each Dn In Rng
If Dn.Value >= MyMax - 0.5 And Dn.Value <= MyMax Then
If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
End If
Next Dn
For Each Dn In nRng.Offset(, CDif).Areas
If Dn(Dn.Count) - Dn(1) > oMax Then
oMax = Dn(Dn.Count) - Dn(1)
Set R = Dn
End If
Next Dn
ReDim Ray(1 To 4, 1 To 2)
Ray(1, 1) = "Level Considered Max": Ray(1, 2) = MyMax - 0.5 & " to " & MyMax & " g/L"
Ray(2, 1) = "Number of Times The Level Hit": Ray(2, 2) = nRng.Areas.Count
Ray(3, 1) = "Longest of Which Lasted for": Ray(3, 2) = R(R.Count) - R(1) & " h"
Ray(4, 1) = "Corresponding Time Window": Ray(4, 2) = R(1) & " to " & R(R.Count) & " h"
With Range("d101").Resize(4, 2) 'Change Location from D101 to suit
.Value = Ray
.NumberFormat = "@"
.Columns.AutoFit
.Borders.Weight = 2
End With
End Sub
```

Last edited by a moderator: