Multiple condtion For loop, dynamic named range

**emburl** · 12-04-2012, 06:38 PM

Hi,

This is my first play with VBA so apologise if the question is very easy and but I've been running into errors I cant resolve for the last couple of days.
(if someone can suggest a good debugging tutorial would also be appreciated).

I have a spreadsheet of building data energy use (15min intervals for many days, I've attached just two days). I'm trying to extract the time ranges where use is high/low or ramping up and down.

All I think I need is a for loop, which loops through a named range and returns the value of the adjacent time column for the first cell which exceeds a bound (in this case this is the variable "mid"), following this I want to continue looping through the range and returns the time value where the value of the range drops below the variable "mid".

My first problem is in this implementation loop, it gives an application/object definition error, which so far I can't resolve.

I've attached the spreadsheet with my current VBA code

Thanks for your time

**rylo** · 12-04-2012, 07:15 PM

Hi

A couple of things.

rng is already a range, so the for loop should be

for each c in rng

2) mid is a key word, so use something else - midd perhaps

3) I've dummied up something that will put a comment in column M when the transition point is met. Hopefully it will give you some ideas.

Sub aaa()
  Sheets("Data").Activate
  midd = Sheets("Out").Range("C1").Value
  For i = 2 To Cells(Rows.Count, "H").End(xlUp).Row
    If Cells(i + 1, "H") > midd And Cells(i, "H") <= midd Then
      Cells(i + 1, "M").Value = "Transition Up"
    ElseIf Cells(i + 1, "H") < midd And Cells(i, "H") >= midd Then
      Cells(i + 1, "M").Value = "Transition Down"
    End If
  Next i
    
End Sub

rylo

**AlphaFrog** · 12-04-2012, 07:19 PM

This fixes the syntax errors. I don't know if it produces the results you expect.

Sub Main()

  Dim Rng As Range
  Dim p As Long, N As Long
  Dim nBase, nPeak, mid As Long
   
  Set Rng = Range("H2:H97")

'form a counter that is as long as The number of days by finding the total entries
    N = WorksheetFunction.CountA(Columns(3)) 'n is the total entrys

'Take number of data points (n) and divide by 96 to get number of days
    days = N / 96

'and work out the upper and lower limits (2.5 and 97.5 percentiles)
  For p = 1 To days
    nBase = WorksheetFunction.Percentile(Rng, 0.025)
    Sheets("out").Cells(p, "A").Value = nBase
    
    nPeak = WorksheetFunction.Percentile(Rng, 0.975)
    Sheets("out").Cells(p, "B").Value = nPeak
    
    mid = nBase + (nPeak - nBase) / 2
    Sheets("out").Cells(p, "C").Value = mid
    
'For the current range (rng) find the first cell in column H which is above mid, return the time
'also find the Cell at which the data drops below the mid, return this time
    For Each c In Rng
        If c.Value > mid Then
            'Cells(p,"D") = The adjacent cell in the E column?
            Exit For
        End If
        'But now I want to keep the loop going and find the time where it crosses below the mid
    Next c
     
    Set Rng = Rng.Offset(96, 0) 'use this to cycle through days (relys on 15min data, will update to recognise ranges based on data in future)
  Next p
  
        'TODO
        'how long is it above this midpoint?
        'how long between rise and fall times?
        
'Reference Testing
'Workbooks("CottonKirkSmallTest").Sheets("data").Range("H2:H97").Font.Bold = False
'Workbooks("CottonKirkSmallTest").Sheets("data").Range("A5:A7").Value = N
'Workbooks("Test").Sheets("Sheet1").Range("A1:D5").Font.Bold = True
            
            
End Sub

You were referencing Workbooks("CottonKirkSmall")
The workbook's name is Workbooks("CottonKirkSmall.xlsm")
The workbook reference is not really necessary unless you are switching between workbooks.

You created a loop For Each c In Range(Rng)
Changed to For Each c In Rng

Debugging VBA

**emburl** · 12-04-2012, 09:59 PM

Thanks rylo and AlphaFrog,

The dual conditions for transition points is perfect. Because I'm working with multiple days in the same file, I think stuck with the named range and offset method. To do this I used rylo's logic for the if conditions, and used the c.Row property to return what cell satisfied the conditions.

        For Each c In Rng
            If c.Value > midp And prev <= midp Then
            TransitionUp = Sheets("data").Cells(c.Row, "E").Value             'c.Row returns the row of the cell which satisfied the condition
            Sheets("out").Cells(p, "G").Value = TransitionUp
            ElseIf c.Value < midp And prev >= midp Then
            TransitionDown = Sheets("data").Cells(c.Row, "E").Value
            Sheets("out").Cells(p, "H").Value = TransitionDown
            End If
            prev = c.Value                                                               'prev is my variable to store the previos cell value for comparison
        Next c

There's undoubtedly more efficient ways to do this, and if anyone wants to suggest those feel free.

Thank you