WorksheetFunction.Large in VBA

austinpcherry

New Member
Joined
Feb 26, 2014
Messages
36
While am I getting an "Invalid Qualifier" error when I try and use the Large function? I am using it like:
Code:
Champ1 = Range(WorksheetFunction.Large(Arg1:=Range("O1", "V129"), Arg2:=1).Offset.End(xlToLeft).Value)

Thank you for your help in advance.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
You have the large function wrapped inside of range.

Range expects a range, large returns a number.

What exactly are you hoping to do?

What is in these cell that are referenced in the large function?
 
Last edited:

austinpcherry

New Member
Joined
Feb 26, 2014
Messages
36
I have a range of numbers that are populated from user entry. The numbers in the range are the number of times a combination of the row header and the column header both appear in the user entry. I then want the row header and the column header of the most entered combinations, hence the Large function and the offset.
So how do I get the address of the Large 1,2,3,4,5 so I can get the row and column headers?

I have Items listed in the row headers and Geographic areas listed in the column headers. When an order is processed it adds one to the cell in the crossing of the item and area. I want to know which item in which area are my top 5 sellers.
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
You'll have to post a screen shot with a small data sample and tell me what you want based on the data shown.

Large is only going to get you a number, you can't get a range from that.

If the number is unique you could use match or find etc. to get a column and/or row from matching the number.

I'm sure we can help you, but we're going to have to see what you are talking about in order to figure out the best code.
 

austinpcherry

New Member
Joined
Feb 26, 2014
Messages
36
Ok, I will have to do that from a different computer later. The numbers definately will not be unique. I tried to use the Large function just on the worksheet. I tried to use indirect(address(Large(blah, blah) and it kind of works, but it does not deal with duplicates that way. It just shows two of the same entry. If I have 20 items sole in the NW region and 20 in the SW region I need to know both of those values, not just NW twice. I tried to use find and findnext but I got lost there as well. When I get to my other computer I will post a screenshot. Maybe it will make more sense then.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,875
Messages
5,834,182
Members
430,263
Latest member
abz54

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