Help to add a different criteria to a existing macro

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:

Private Sub Worksheet_Calculate()

If Range("AF2") <> Range("AF4") Or Range("AG2") <> Range("AG4") Or Range("AD2") <> Range("AD4") Then
    Application.EnableEvents = False
    Range("AD2:AG2").Value = Range("AD4:AG4").Value
    Range("B2:H5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("AF3:AG4"), _
        CopyToRange:=Range("AH2:AN2"), Unique:=False
    
    Application.EnableEvents = True
End If

Many thanks

Hi, bazofio,

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

Posting code between [CODE] [/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

Sorry Holger, i have edited my post.

Can someone give some help please.

Thanks

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

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

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AC2:Ac3")) Is Nothing Then
  If Target.Count = 1 Then
    Application.EnableEvents = False
'    Range("AD2:AG2").Value = Range("AD4:AG4").Value

    Range("B2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("AF3:AH4"), _
        CopyToRange:=Range("AI2:AO2"), Unique:=False

    Application.EnableEvents = True
  End If
End If
End Sub

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

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

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?