Avatar of Raahaugen
Raahaugen
Flag 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
Microsoft Excel

Avatar of undefined
Last Comment
Raahaugen

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Steve

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
Raahaugen

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck