Excel: How to find a maximum value with VBA? 3

[xitu]

This is my sample block. "HP" marks value > 11300. How do I find maximum value in HP range and mark the word "MAX" next to it. How do I loop through the whole worksheet?

ColA ColB ColC
================================
10966.327
11176.779
11267.391
11305.390 HP
11404.770 HP
11477.844 HP
11485.638 HP MAX
11442.769 HP
11451.537 HP
11444.717 HP
11443.742 HP
11437.896 HP
11446.665 HP
11441.794 HP
9301.220
8962.157
8653.299
8335.671
8030.710
7735.492
7376.943
================================

Sub findMax()
While ActiveCell.Offset(0, -2).Value > 0 'activecell is col C
If ActiveCell.Offset(0, -1).Value = "HP" Then
'find maximum value
'I don't know the syntax here:
'If ActiveCell.Offset(0, -1) Is max Then ActiveCell.Offset(-1, 1).Value = "MAX"

End If
ActiveCell.Offset(1, 0).Select
Wend
End Sub

 

[Zathras]

Thanks Skip... I was starting to get bored with this. And anyway, graphing is not my strong suit.

 

[SkipVought]

No prob. I have been monitoring the convesation and noticed that you were "away."

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xitu]

It works like a charm!

Yes, Skip. Could you post (or send me) the code for multiple series formatted, please?

Many thanks to Zathras and Skip.

 

[SkipVought]

Dim rngTime As Range, rngPressure As Range
Sub HP()
    Const SEC_PER_DAY = 86400
    r1 = 1
    r2 = 0
    nSeries = 0
    Do While r1 <= ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        If Cells(r1, 2).Value = &quot;&quot; Then
            r1 = Cells(r1, 2).End(xlDown).Row
            If r1 = Cells.Rows.Count Then Exit Do
            r2 = r1
            If Cells(r1, 2).Offset(1, 0).Value = &quot;HP&quot; Then
                r2 = Cells(r1, 2).End(xlDown).Row
            End If
            Set rng = Range(Cells(r1, 1), Cells(r2, 1))
            nMAX = Application.Max(rng)
            r = Application.Match(nMAX, rng, 0)
            If Not IsError(r) Then
                Cells(r + r1 - 1, 3).Value = &quot;&quot;&quot;MAX&quot;&quot;&quot;
                Cells(r2, 3).Value = &quot;&quot;&quot;END&quot;&quot;&quot;
                t = 0
                Set rngTime = Range(Cells(r + r1 - 1, 4), Cells(r2, 4))
                Set rngPressure = Range(Cells(r + r1 - 1, 5), Cells(r2, 5))
                pBase = Cells(r + r1 - 2, 1).Value
                For Each c In rngTime
                    With c
                        .Value = t
                        .NumberFormat = &quot;[h]:mm:ss&quot;
                        .Offset(0, 1).Value = pBase - Cells(.Row, 1).Value
                    End With
                    t = t + 3 / SEC_PER_DAY
                Next
                nSeries = nSeries + 1
                FormatSeries nSeries
            End If
            r1 = r2 + 1
        End If
    Loop
End Sub
Sub FormatSeries(n)
    With ActiveSheet.ChartObjects(1).Chart
        If n > .SeriesCollection.Count Then
            .SeriesCollection.Add Source:=rngPressure.Address(external:=True)
        Else
            For i = 2 To n
                .SeriesCollection(i).Delete
            Next
        End If
        .SeriesCollection(n).XValues = rngTime
        .SeriesCollection(n).Values = rngPressure
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xitu]

Skip,

Since your code is difficult to understand, I have to stick with Zathras's code.

I played with Zathras's code but could not figure out how to add Series chart (in column E and F for each block)

Could you, Zathras or anyone can help me?

Thanks a bunch.

Here's the latest code:

http://viet.ws/chart.xls

Option Explicit
Const PRESSURE_DATA_COLUMN = "B:B"
Const HIGH_PRESSURE = 11300
Const HP_SYMBOL = "HP"
Const START_SYMBOL = """START"""
Const END_SYMBOL = """END"""
Const TICK_INTERVAL = 3 '(seconds)
Const SECONDS_PER_DAY = 86400

Sub FindBlocks()
Dim c As Range
Dim nStartValue As Double
Dim nStartAddress As String
Dim iSeconds As Integer
Dim iTime As Double

Application.ScreenUpdating = False
nStartValue = 0
For Each c In Intersect(ActiveSheet.UsedRange, Range(PRESSURE_DATA_COLUMN))
If c.Value > 0 Then
' Set clock start for the whole worksheet 0:00:00 with increment is 3
iTime = iSeconds / SECONDS_PER_DAY
c.Offset(0, 7) = WorksheetFunction.Text(iTime, "h:mm:ss&quot
iSeconds = iSeconds + TICK_INTERVAL
End If

If c.Value > HIGH_PRESSURE Then
' Mark "HP"
c.Offset(0, 1) = HP_SYMBOL
If c > nStartValue Then
nStartValue = c
nStartAddress = c.Offset(0, 2).Address
End If
Else
' Clear "HP" and set "Start" (with time ticks)
c.Offset(0, 1) = ""
StartTimeTicks nStartAddress
nStartValue = 0
End If
' Clear "Start"
c.Offset(0, 2) = ""
Next c
' Set "Start" (with time ticks)
StartTimeTicks nStartAddress
Application.ScreenUpdating = True
End Sub


Private Sub StartTimeTicks(StartAddress As String)
Dim rng As Range
Dim nSeconds As Integer
Dim nTime As Double
Dim pBase As Double
Dim tbase As Double

If StartAddress <> &quot;&quot; Then
Set rng = Range(StartAddress)
pBase = rng.Offset(0, -2).Value
tbase = rng.Offset(0, -3).Value
rng.Value = START_SYMBOL


While rng.Offset(0, -1) = HP_SYMBOL
nTime = nSeconds / SECONDS_PER_DAY
rng.Offset(0, 1) = WorksheetFunction.Text(nTime, &quot;h:mm:ss&quot
rng.Offset(0, 2) = pBase - rng.Offset(0, -2).Value ' Calculate Diff. P
rng.Offset(0, 3) = tbase - rng.Offset(0, -3).Value ' Calculate Diff. T
Set rng = rng.Offset(1, 0)
nSeconds = nSeconds + TICK_INTERVAL
Wend


If rng.Offset(-1, 0) <> START_SYMBOL Then
rng.Offset(-1, 0) = END_SYMBOL
Else
rng.Offset(-1, 0) = START_SYMBOL & &quot; (&quot; & END_SYMBOL & &quot&quot;
End If
Set rng = Nothing
End If
End Sub

 

[SkipVought]

What is hard to understand about my code?

I will explain.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com