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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

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