Max Problem within a range..................

[j33pguy]

Hi,
If i have a range called "Range1", How can i say the following:

-find the cell in range1 that has the max value within that range

-and then do a process (which i have already done) for the columns directly to the left of that cell.


would it be something like:

dim num as double
num= worksheefunction.Max(range1)

??

 

[j33pguy]

ray:

 

[j33pguy]

i tried the following but i'm getting errors in the syntex i think!


instead of this code:

Do Until ffstr = goal

For Each Cell In rngAh


If Cell.Offset(0, -1) = WorksheetFunction.Max(rngAg) Then


        cc = 1 'Set cc equal to 1 because of the following code...
        
        While Cell.Offset(0, (-cc - 1)).Value = ""
                cc = cc + 1
           Wend
            If Cell.Offset(0, -cc - 1).Value = 1 Then
                Cell.Offset(0, -cc - 1).Value = ""
            Else
                Cell.Offset(0, -cc - 1).Value = Cell.Offset(0, -cc - 1).Value - 1
            End If
 Exit For
 End If
 
Next Cell
Loop

i tried to write it as:

Do Until ffstr = goal

dim num as range
num = worksheetfunction.max(rngAg)

        cc = 1 'Set cc equal to 1 because of the following code...
        
        While num.Offset(0, (-cc - 1)).Value = ""
                cc = cc + 1
           Wend
            If num.Offset(0, -cc - 1).Value = 1 Then
                num.Offset(0, -cc - 1).Value = ""
            Else
                num.Offset(0, -cc - 1).Value = num.Offset(0, -cc - 1).Value - 1
            End If
 Loop

what am i doing wrong??

 

[j33pguy]

ok....this is what i tried next.....it still didn't work

Do Until ffstr = goal 
dim z as long, w as long
dim c as range


z = Application.WorksheetFunction.large(Range("rngAg"))
While z.Offset(0, (-cc - 1)).Value = "" 
                cc = cc + 1 
           Wend 
            If z.Offset(0, -cc - 1).Value = 1 Then 
                z.Offset(0, -cc - 1).Value = "" 
            Else 
                z.Offset(0, -cc - 1).Value = z.Offset(0, -cc - 1).Value - 1 
            End If 
 Loop

i'm running out of ideas
please help

 

[Scott Huish]

Couple questions:

Is the named "rngAg" always only in a single column?

When you're working backward through the columns do you want the next column to be searched to contain the same number of rows as "rngAg" or the entire column? For example if "rngAg" is C7:C37, do you then want to check B7:B37 then A7:A37 or all of Column B and A?

What do you want to do with these values once you've found them?

 

[acw]

Hi

The following will find the max value in the range, and the address of the cell that contains it. It does assume that the max is unique, or if it is not unique, it will find the first instance of the max.

Once you have the address of the cell you can work from there to do your next process.

Sub aaa()
num = WorksheetFunction.Max(Range("range1"))
Set ca = WorksheetFunction.Index(Range("range1"), WorksheetFunction.Match(num, Range("range1"), 0))

MsgBox ca.Address

End Sub

Tony

 

[j33pguy]

Quick comment for ACW...
thank you for your code but when i try that i get this error:
"Method 'Range' of object'_Global' failed"
and then it highlights num=worksheetfunction.max(range("rngAg"))


To answer HOTPEPPER's question, "rngAg" is always inly in a single column. the range changes....but rngAg is always in the same column. and to answer the second part of your question, when i'm working backwards, the range will be the same as rngAg in the left columns....so if rngAg is C7:C37, then the same range in B and A columns will be checked (i.e B7:B37 and A7:A37)
once the max value in rngAg is found, then the process goes to the left cell, if it's empty, it moves to the next left cell....once it finds a number, it reduces that number by one unit and then recalculates the numbers in rngAg and does the same process again and again until FFSTR is equal to Goal.

 

[j33pguy]

 

[acw]

Hi

What is the name of the range? In your first mail, it was called Range1 and I build my code around that.

My guess is that rngAg is a variable within the macro. If this is the case, then it can't be used in the way you have tried.

So if the name is Range1, then try my code.


Tony

 

[j33pguy]

RngAg is a variable within the macro....it's a dynamic range that changes based on different situations....but it's always in the same column "AZ"...
it could be AZ6:AZ20 and then AZ21:AZ24, etc...
right now, what i have the code doing is check each line of the range in rngAg and if it is the biggest, then "the Process" is done for that particular row....and this takes time because the code has to check like 20 rows before it finds the max in that range. what i wanted to figure out was a way to find the max right off the bat, and therefore avoid the time consuming line by line check!

is that possible?