Using VBA to filter multiple criteria and then count number of rows with data

You need to use Range like

Please Login or Register  to view this content.

Also please amend you post to put the code in code tags, the # icon in the reply window

You need to edit both your posts, select the code & then click the # icon in the reply/edit window.
If done correctly it should then appear in a "window" similar to line of code in post#2

hello Fluff13, i have correctly tagged the code.

Regards

Hi, to put the value of t into another workbook, that line should be

Please Login or Register  to view this content.

not the other way round.

Thanks for the help so far! I feel like I'm almost there. I get an error in the last line of code. Do I have first to activate the sheet "Leitfaden"

Please Login or Register  to view this content.

You're missing the quotes around the sheet name

Hello, thanks that worked.

I have just about the same piece of code as before , only the last line is different which is where the value t is being pasted to. However I get an error stating the "index is out of range".

Please Login or Register  to view this content.

That's not an error I remember seeing, what line is highlighted when you hit debug?

I have underlined the line of code

Please Login or Register  to view this content.

Originally Posted by ajohns52

I have underlined the line of code

Please Login or Register  to view this content.

Maybe like this

Please Login or Register  to view this content.

hello, yeah it still doesn't work. Weird thing is as soon as i switch the last line of code by only pointing to another worksheet, it all works.

The following works in the above code:

Please Login or Register  to view this content.

In that case it sounds like you have the sheet name spelt wrongly.
Should the underscores be there?
Also check for leading/trailing spaces

yeah it doesn't work for some reason. I even changed the worksheet name to just "Report" and it still doesn't work. I'll look more into it and let you know if i find a solution

and the official error which i didn't state correctly is:

Run-time Error 9 "Subscript Out of Range"

With the "Report_Inventory_Stock" sheet active run this

Please Login or Register  to view this content.

Then copy what was written in the Immediate window in the VBE, to the thread.

i don't know if this is what you're looking for. There are several Windows open in the console

Please Login or Register  to view this content.

That's exactly what I was looking for, thanks.
You have a space at the end of the sheet name, which is why you are getting the error.

that's it! great tool to use. I will keep this in my box of tools!

Thanks

Glad to help & thanks for the feedback.

I would also recommend using the file extension when referring to workbooks

Please Login or Register  to view this content.

with out the extension, if somebody with different folder settings tries to use your code it may still fail.

okay thanks for the recommendation. Another quick statement. The ultimate goal of what I'm trying to do is copy and paste these values to the worksheet "inventory_stock_report" for each month. In the inventory_report_stock worksheet i have the months January, February, March etc. Now imagine for the above code, the copy and paste is hard-coded (i.e. put the value t in cell Q2): However for next month, the cell will probably be R2. That means I would have to go in the code and change the code from Q2 to R2.

Do you know of any resources where I could learn about the dynamic changing of cells. I would ideally like to push a button and then populate the list for the month that I'm working in. And then when November comes around I would do the same thing.

Regards

If the column headers are text, rather than dates, you could use something like

Please Login or Register  to view this content.

Hello I took some of your advice from the above code,

I'm trying to find the letter A in the range B11:B14, and if I find it I would like to go to column 6 (staying in the same row) and grab that value. So basically if there was an "A" in B12, I would like to grab the value in F12 and store it to the variable partA_quantacc1. I'm receiving an error in the "If Application.Match" piece of code. I underlined it.

Please Login or Register  to view this content.

If you get an error on that line I suspect it's because it find "A" in the range

exactly.... I want it to find an A in the range, so why wouldn't it continue with the rest of the code in the IF Statement?

and there is no "A" in the range B11:B14 currently, so it should follow through to the else Statement...correct?

Nope, the Match will generate an error if nothing is found.
Do it like

Please Login or Register  to view this content.

You don't need a loop, you can just use AverageIf

Please Login or Register  to view this content.

haha thanks! It works! just one line of code.

However just out of curiousity, I would like to know how I would store it in an array. This code below works but everytime there is a new value for Department A, the value overwrites the previous value for Department A.

Please Login or Register  to view this content.

I think it's time to start a new thread for this, as we have gone a very long way from the original question.

I tried and failed to do this..

contactforinfo
Please do not "hijack" other peoples threads.
You will need to start a thread of your own.

you're absolutely right. I'll start a new thread. Thanks for all of your help

You're welcome

hello Fluff13,

I am trying to start a new thread but i can't seem to find the button. Is there a maximum number of threads that I can start? It says you're a Forum Expert so I'm asking you.

Originally Posted by ajohns52

Is there a maximum number of threads that I can start?

Not that I'm aware of.