Raahaugen

asked on

# Vba function to make range based on criteria

Hey Experts!

Scenario: I have one table with lots of records. In one row I have Initials, and in another row I have Department. The order is random.

Problem: In other places in the workbook I want to use individual ranges with all the initials in a given department, etc. using the index() excel function.

Trying to write the function in semi VBA and semi plain english:

public function range.if(rng as range, criRng as range, cri as string)

for each c in rng

if c in criRng = cri then include c in output

exit for

range.if = output

end function

Regards Raahaugen

Scenario: I have one table with lots of records. In one row I have Initials, and in another row I have Department. The order is random.

Problem: In other places in the workbook I want to use individual ranges with all the initials in a given department, etc. using the index() excel function.

Trying to write the function in semi VBA and semi plain english:

public function range.if(rng as range, criRng as range, cri as string)

for each c in rng

if c in criRng = cri then include c in output

exit for

range.if = output

end function

Regards Raahaugen

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi,

Sorry I didn't close this question before now. Your idea became handy when making my solution, so here is my thanks!

Best reagards

/Raahaugen

Sorry I didn't close this question before now. Your idea became handy when making my solution, so here is my thanks!

Best reagards

/Raahaugen

ASKER

But i do think maybe naminge the range sounds like a good idea - then the range will only have to be calculated once - and not once for every formula I use it.

I'll have a look at your code - but so far it looks like i can use it.

Thanks!