Find Max value in a changing (variable range)

**Zambo** · 05-11-2009, 02:38 PM

I've been searching for a few days trying to figure this out however my feeble brain cannot solve this.

Here's the deal..... I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty!

I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part.

So far I have tried:

For EMA_20 = 20 To 28
              
         MaxVal =  Sheets(rv1).Range(Sheets(rv1).Cells(EMA_20 - 13, 3), Sheets(rv1).Cells(EMA_20, 3)).Max
        rv1.Cells(EMA_20, 7) = MaxVal        
        
    Next

No dice... so I tried:

For EMA_20 = 20 To 28
        
        MaxVal = Application.WorksheetFunction.Max(Range(rv1.Cells(EMA_20 - 13, 3), rv1.Cells(EMA_20, 3)))
        rv1.Cells(EMA_20, 7) = MaxVal
          
    Next

Finally:

For EMA_20 = 20 To 28
        
       Dim MyRange As Range
       Dim Startrng
       Dim Endrng
        
       Startrng = EMA_20 - 13
       Endrng = EMA_20

Set MyRange = Range(Cells(Startrng, 3), Cells(Endrng, 3)) 

MaxVal = MyRange.Find(What:=WorksheetFunction.Max(MyRange), LookIn:=xlValues)

rv1.Cells(EMA_20, 7) = MaxVal
        
    Next

Most of the time I get runtime error 9 Subscript out of range.

The fix is probably very simple but like I said, I have a feeble mind.

Thanks in advance for any help/hints!!

Robert

**shg** · 05-11-2009, 03:04 PM

Welcome to the forum.

It's a little hard to tell what you're trying to do. Maybe this:

    Dim iRow    As Long

    For iRow = 20 To 28
        With Worksheets("rv1")
            .Cells(iRow, "G") = WorksheetFunction.Max(.Cells(iRow - 13, "C").Resize(14))
        End With
    Next

**Zambo** · 05-12-2009, 11:36 AM

Awesome! Thanks for the help! I ended up modifying the code slightly and now have:


Dim MaxVal as Double
For EMA_20 = 20 To 28

MaxVal = WorksheetFunction.Max(rv1.Cells(EMA_20 - 13, 3).Resize(14))
rv1.Cells(EMA_20, 7) = MaxVal

Next

Works like a champ....However I want to learn this stuff and the .Resize(14) command I don't completely understand. Actually it appears pretty basic in that it takes the starting cell and then selects the following cells up to the resize number. Is this the only way to enter a moving range in a Max (or Min) function. Granted the way it is now looks like the simplest and is the code I will use, however for my understanding of VBA and Excel Macros I was wondering if that is it?

Thanks again for the awesome quick response!

Robert

**shg** · 05-12-2009, 07:42 PM

There's lots of ways to specify a range, including rng.resize(#rows, #cols), Range(cell1, cell2), rng.offset(#rows, #cols).

If you have a specific further question, ask away. If not, would you please mark the thread as Solved (How To on the menu bar)?