Link to home
Start Free TrialLog in
Avatar of Raahaugen
RaahaugenFlag for Denmark

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
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Raahaugen

ASKER

But how should i make the range with sumifs or countifs?

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!
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