Help to add a different criteria to a existing macro

[bazofio]

Hi guys.
I need some help to add a new criteria, if possible, to this macro. What it does now is to when there is some change in some cells it will make a filter to a database to get all the values that match the criterias. Ill try to explain better.....So this is how it works, when one or all the cells "AF2", "AG2" and "AD2" are different then "AF4", "AG4" and "AD4" the macro make the filter in the database (B2:H5000) and gives me the values that match those criteria that is the AF3:AG4 range (values in AF4 and AG4).

What im trying to do is that the macro make the filter searching to match exactly equal the value in AF4 (like its doing now) but in AG4 instead using that value what i need is that macro make the search with a interval, i mean supose that the value is 16, what i want is that it uses this value to search between 14 and 18 (equal to 14,15,16,17 and 18. 16+2 and 16-2) to give me more results in the end. Because now im limited to a single value to meet the criteria and im getting few results.
Is this possible?
This is my macro:

Please Login or Register  to view this content.

Many thanks

[HaHoBe]

Hi, bazofio,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[bazofio]

Sorry Holger, i have edited my post.

Can someone give some help please.

Thanks

[HaHoBe]

Hi, bazofio,

using the Advanced Filter there should be 2 general ways to achieve what you want. No. 1 is to use as many rows as you want the range to go with basicly the same setting for most of the criteria and augment one for the range you want to be shown. No. 2 would be to double the exact heading next to the criteria range and use greater or equal to the starting range under the first criteria and less tahn equal to the maximum value in the second. Please make sure to adapt the criteria range to extra column.

Ciao,
Holger

[bazofio]

Many thanks Holger
Can you please give some example for this two ways you said. Im not very good with VBA and this was a existing macro that im trying to improve.

[HaHoBe]

Hi, bazofio,

maybe attach a sample of your workbook - I would need to sit down and create a workbook wit mock data while you already have created one.

Ciao,
Holger

[bazofio]

Holger
I have attach a sample and made just some changes to the source values to search because the workbook is big, so i have attached just that sheet with the macro and its just working for the first code (there are 3 that are basically the same, just changes the search range).

Thanks again for your time ;-)

[HaHoBe]

Hi, bazofio,

regarding the original post and the question there: keep it simple and stupid.

Check the range where the entries will be made instead of trying to capture the calculatiuon for formulas based on these cells.

I inserted a new Column at AH and altered the formulas for AF2 as well as AF4.

Please Login or Register  to view this content.

Please note that I commented out the code line which converts the formula to value. You could add that to the macro as either a formula being written to the cells or as values being entered there.

Ciao,
Holger

[bazofio]

Hi Holger

So its more or less this changes that you made, but you made it for "Tir.Roun." but it was for %Casco. Can you please check that because i dont understand very well what you have done (sorry, im a noob in this things).
You have just change the range in the original macro? The macro you post does it do the same thing that the original one?

Sorry once again

Many thanks

[HaHoBe]

Hi, bazofio,

is it really so big a problem copying over the header from AG3 to AH3 and change the formulas in AF4 to AG4?

Please mind that some data in the workbook is deleted (as well as a couple of the formulas for the resuilt), and that I zipped the workbook for size.

Ciao,
Holger

[bazofio]

Ok i understand now because i was not seeing your code, it was in the end of the original ones.
In the original code when i have some change in the values ("Tir." and "%Casco") or in the date the macro starts a new search automatically. Does this going to happen also with your code? Can i delete the original one or i must have it working with yours?

[HaHoBe]

Hi, bazofio,

I mentioned before that only a new entry or edit of the cells AC2 or AC3 would raise an event to start the macro. I canīt figure out why the changing of the date should have any effect as the date doesnīt exist in the date column in the data if I remember correctly.

Ciao,
Holger

[HaHoBe]

Hi, bazofio,

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...-criteria.html

Ciao,
Holger

[bazofio]

Hi Holger

The changing of the date is used because when i open the workbook everyday i want all the data updated even when there isnīt any change in AC2 or AC3, because this way i can have the data that could meet the criteria from the day before.
The original macro does this. I have tried just with your macro and it wasnīt working, but i made some changes in the original one and for now its ok but need some further testing.

PS: sorry for the crosspost but i was thinking that i was posting here in the first place, when i see that i was wrong i have posted here but should have erase the other post in the other forum because its here where i use to come.