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

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
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)

??
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

j33pguy

Well-known Member
Joined
Aug 6, 2004
Messages
633
i tried the following but i'm getting errors in the syntex i think!


instead of this code:

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:

Code:
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

Well-known Member
Joined
Aug 6, 2004
Messages
633
ok....this is what i tried next.....it still didn't work :(

Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Aug 6, 2004
Messages
633

ADVERTISEMENT

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.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Aug 6, 2004
Messages
633
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,770
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top