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!