Setting up Dynamic Ranges from Cells (Excel 14, VBA Only)

I need to setup a dynamic range based on another dynamic range. I also need to make the code universal so I can plug it easily into other worksheets.

Previously I used the following code, which is static & relies on the concentate of 3 user defined values.

Please Login or Register  to view this content.

I am looking to do something more like this (this code looks at a range of numbers for matches and returns all names with that number), but instead of returning the values for a dropdown list I need the dynamic range setup (i.e. not values but cell references). This range will then be used in a lookup to provide the actual range value for another vlookup.

Please Login or Register  to view this content.

It will use the attached ArrayList worksheet. Based on the ADMIT TYPE it will collect the range of all the TRAUMA, or STANDARD etc to then be used in another Vlookup based on the GEOZIP to return the ARRAY value.

I realize I may be confusing some terms like array & range in my usage. Please forgive the Newb.

Hi

Not sure that I really follow what you are trying to do. If you really want to extract the value from column C, based on the data in columns A and B, then why don't you use sumproduct (or an array entered formula) to return the row of the match, and then use INDEX to return the value from column C? Once you have that, then you can use INDIRECT to make it relevant range?

If that isn't it, then how about a step by step example on exactly what you want to get for a specific data set.

rylo

I have no idea what you just said as I am a newb.

I want to setup the SERVICE TYPE dynamically (for future years there may be changes in the data) to create the Range needed for the Vlookup to use GEOZIP as the criteria to return the "array" for the 2nd lookup. For example, if the SERVICE TYPE is trauma and the GEOZIP is 605 then it should return "D480390:F480948."

So I need to it to find/match/count/?? all the TRAUMA to return a Range so that I can use GEOZIP in a lookup. There are 4 service types, each with 29 GEOZIP codes that are the same.

Hi

Say you insert a new sheet into your example workbook, and put TRAUMA in A1, and 605 into B1. Enter the formula below and it will return the range required.


=INDEX(ARRAYS!C:C,SUMPRODUCT(--(ARRAYS!A1:A116=Sheet1!A1),--(ARRAYS!B1:B116=Sheet1!B1),ROW(ARRAYS!A1:A116)))

rylo

I am trying to get rid of this line (as it's static)

Please Login or Register  to view this content.

So by using the admit_type (2006-2009 were STANDARD, TRAUMA, 2009 & following STANDARD, TRAUMA, HCPCS, OUTPATIENT, ANESTHESIA, PROFESSIONAL) and dyear you get a range of GEOZIP cells. The 2nd vlookup (or whatev) use this range and the geozip to come up with resulting range.

It should all be in VBA (not in cells).

The sheets array is password protected (and always hidden). There is no need to alter data within it.