VBA: Multiple 'If' + 'And' + multiple 'For Each': Please send help.

Y

YourBroLucas

New Member
Joined
Jul 11, 2022
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Howdy,

So I have this Macro supposed to filter and paste a general dynamic table for 2 criteria in two different fields.

I want both If statements to be true in order to copy/paste the data, so I tried the following macro.

Notes:
  • There is no error message, but nothing happens.
  • Fields: Dir and Type, c = criteria, s = selected
  • Criteria are selected from dropdown lists in shtTotal B6 and B7

  • I suppose that I need to put two 'For Each' loops since they deal with different arrays.
  • → Thus I suppose I need to put 'Next sType' just like I put 'Next sDir', but how and where?
  • I wonder if it is possible to do the same thing with even more criteria, like 5 of them.
I've also tried these structures:
  1. For Each 1 (For Each 2 (If 1 (If 2 (Action)))
  2. If 1 AND If 2 (For Each 1 (Action); For Each 2 (Action)
  3. If 1 then (For Each 1 (Action)), If 2 then (For Each 2 (Action))
  4. For Each 1 (If 1 then (Action)), For Each 2 (If 2 then (Action))

  • As a sidequest, I have issues with shtGen.ShowAllData, often gives an error message, when nothing is hidden in the sheet.
  • → Thus I wish to create an If statement to verify if any row is already hidden before executing this line.
In addition, if you just so happen to have extra time, this is part of a bigger macro I'm trying to pull off.
www.mrexcel.com

Copy/paste dynamic, filtered data + put a column into next row (through macro)

Howdy dear forum members, Odd title I agree. So I have sheet 1, sheet 2, and sheet 3. Sheet 1 is the overall data entry dynamic table. Data is added through a User form (button "Add new entry") Sheet 2 is where filtered data (per department transactions) is exported through a macro (see...
www.mrexcel.com www.mrexcel.com

With love ♥,
Lucas

VBA Code: 
Sub iNeedHelpForTheLoveOfGod()

' PART ONE: Multi-criteria export
' Sheets
    Dim shtGen As Worksheet
    Dim shtTotal As Worksheet
    Dim shtVar As Worksheet
    Set shtGen = ActiveWorkbook.Worksheets("Tab_Général")
    Set shtTotal = ActiveWorkbook.Worksheets("RECAP_TOTAL")
    Set shtVar = ActiveWorkbook.Worksheets("Variables")
'
' Arrays
    Dim arrDir As Variant, sDir As Variant
    Dim arrType As Variant, sType As Variant
    arrDir = Array("DICOM", "DAP", "DSJ", "DPJJ", "PVAM")
    arrType = Array(shtVar.Range("C2:C19"))
'
' Dynamic Range
    Dim LastRowGen As Long
    Dim LastRowTotal As Long
    Dim LastColumn As Long
    LastRowGen = shtGen.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRowTotal = shtTotal.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'
    Dim RngGen As Range
    Dim RngTotal As Range
    Set RngGen = shtGen.Range("A16:P" & LastRowGen)
    Set RngTotal = shtTotal.Range("A16:P" & LastRowTotal)
'
' Criteria
    Dim StartCell As Range
    Dim cDir As Range
    Dim cType As Range
    Set StartCell = shtGen.Range("A16")
    Set cDir = shtTotal.Range("B6")
    Set cType = shtTotal.Range("B7")
'
' Filter
For Each sDir In arrDir
    If sDir = cDir And sType = cType Then
        RngTotal.Delete
        RngGen.AutoFilter
        RngGen.AutoFilter Field:=3, Criteria1:=sDir
        RngGen.AutoFilter Field:=14, Criteria2:=sType
        RngGen.Copy
        shtTotal.Range("A16").PasteSpecial Paste:=xlPasteAll
    Else
    End If
Next sDir
'
' Return to normal in shtGen
    Application.CutCopyMode = False
'    shtGen.ShowAllData
    shtGen.AutoFilterMode = False
'
' PART TWO: Put column P in next row?!
' PART THREE: Auto cell format
' PART FOUR: Success!
'
End Sub

I managed to make it work with one criterion just fine, but not two.

VBA Code: 
' The filter section that works
For Each sDir In arrDir
    If sDir = cDir Then
        shtTotal.Range("A16:P" & LastRowTotal).Delete
        shtGen.Range("A16:P" & LastRowGen).AutoFilter
        shtGen.Range("A16:P" & LastRowGen).AutoFilter Field:=3, Criteria1:=sDir
        shtGen.Range("A16:P" & LastRowGen).Copy
        shtTotal.Range("A16").PasteSpecial Paste:=xlPasteAll
    Else
    End If
Next sDir

VBA Code: 
' The filter section that doesn't work (detailed)
For Each sDir In arrDir
    If sDir = cDir And sType = cType Then
        shtTotal.Range("A16:P" & LastRowTotal).Delete
        shtGen.Range("A16:P" & LastRowGen).AutoFilter
        shtGen.Range("A16:P" & LastRowGen).AutoFilter Field:=3, Criteria1:=sDir
        shtGen.Range("A16:P" & LastRowGen).AutoFilter Field:=14, Criteria2:=sType
        shtGen.Range("A16:P" & LastRowGen).Copy
        shtTotal.Range("A16").PasteSpecial Paste:=xlPasteAll
    Else
    End If
Next sDir
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Let's see if this is what you need.
In this line you only have one value for the filter:
Set cType = shtTotal.Range("B7")

Then it is not necessary to do a cycle, you are only going to filter by what is equal to cDir.

Either way, I don't understand why you want a filter with the values in arrType
Do you want to make a filter for a type, do actions and repeat the cycle?

If the above is correct try the following macro, I made some adjustments.
VBA Code: 
Sub iNeedHelpForTheLoveOfGod()
  Dim shtGen As Worksheet, shtTotal As Worksheet, shtVar As Worksheet
  Dim arrDir As Variant, sDir As Variant, arrType As Variant, sType As Variant
  Dim LastRowGen As Long, LastRowTotal As Long
  Dim RngGen As Range, RngTotal As Range, StartCell As Range
  Dim cDir As Range, cType As Range

' PART ONE: Multi-criteria export
' Sheets
  Set shtGen = ActiveWorkbook.Worksheets("Tab_Général")
  Set shtTotal = ActiveWorkbook.Worksheets("RECAP_TOTAL")
  Set shtVar = ActiveWorkbook.Worksheets("Variables")
'
' Arrays
  arrDir = Array("DICOM", "DAP", "DSJ", "DPJJ", "PVAM")
  arrType = shtVar.Range("C2:C19").Value
'
' Dynamic Range
  If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  If shtTotal.AutoFilterMode Then shtTotal.AutoFilterMode = False
  LastRowGen = shtGen.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  LastRowTotal = shtTotal.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  Set RngGen = shtGen.Range("A16:P" & LastRowGen)
  Set RngTotal = shtTotal.Range("A16:P" & LastRowTotal + 1)
'
' Criteria
  Set StartCell = shtGen.Range("A16")
  Set cDir = shtTotal.Range("B6")
  Set cType = shtTotal.Range("B7")
'
' Filter
  For Each sType In arrType
    shtTotal.Range("A16:P" & shtTotal.Range("N" & Rows.Count).End(3).Row + 1).ClearContents
    RngGen.AutoFilter
    RngGen.AutoFilter Field:=3, Criteria1:=cDir
    RngGen.AutoFilter Field:=14, Criteria1:=sType
    LastRowGen = shtGen.Range("N" & Rows.Count).End(3).Row
    If LastRowGen > 16 Then
      RngGen.Copy
      shtTotal.Range("A16").PasteSpecial Paste:=xlPasteAll
      '
      'some actions
      '
    End If
  Next
'
' Return to normal in shtGen
  Application.CutCopyMode = False
  shtGen.AutoFilterMode = False
'
' PART TWO: Put column P in next row?!
' PART THREE: Auto cell format
' PART FOUR: Success!
'
End Sub

If the above is not what you need, explain what you need and if possible give examples of all the sheets, what you have in each sheet and what you expect from the result, use the XL2BB tool.

---------------
NOTE XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hello Dante,

Thank you for your answer!
I don't have the file on my personal computer thus I'm going to test it tomorrow with great interest.

In the meantime, I'd like to point out that there is only one value in Set cType = shtTotal.Range("B7") because it is the dropdown list cell, just like B6 (for Dir).
The array of said list is found in arrType = Array(shtVar.Range("C2:C19")), though I don't know if it makes any difference.

As an example, I wish to run it this way:
  1. Select the criteria from the two dropdown lists (e.g. "DICOM" for Dir, and "Goodies" for Types)
  2. Run the macro and check the shtGen's table
  3. Copy/Paste to shtTotal according to the filters
  4. Next step would then be to automatically put the column P ("Description") of each filtered row just below said row.

So if the macro filters and pastes 4 results, the table should be 8 rows long.

Sincerely,
 
Upvote 0
YourBroLucas said:
Select the criteria from the two dropdown lists (e.g. "DICOM" for Dir, and "Goodies" for Types)
Click to expand...

For that, you only need the following:
VBA Code: 
Sub filterdata()
  Dim shtGen As Worksheet, shtTotal As Worksheet
  Dim lr As Long
 
  Set shtGen = ActiveWorkbook.Worksheets("Tab_Général")
  Set shtTotal = ActiveWorkbook.Worksheets("RECAP_TOTAL")
  
  If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  lr = shtGen.Range("C" & Rows.Count).End(3).Row
  shtTotal.Range("A16:P" & Rows.Count).ClearContents
  
  shtGen.Range("A16:P" & lr).AutoFilter 3, shtTotal.Range("B6").Value
  shtGen.Range("A16:P" & lr).AutoFilter 14, shtTotal.Range("B7").Value
  shtGen.AutoFilter.Range.Copy shtTotal.Range("A16")
End Sub
 
Upvote 0
DanteAmor said:
For that, you only need the following:
Click to expand...
Hello Dante,

So I have tried your solution and it works!
(It seems so evident now that I see it)

Now I have yet to figure out how to put column P into next row, if that is even possible...

A few optimization goals:
  • .ShowAllData:
I'll try to add an IF statement before shtGen.ShowAllData to avoid the error message if (for instance) the general table does not find anything when the combination of the 2 criteria does not return any result.

  • Non-existent combination:
I'll try to work with IFERROR and a message box. Navigating on sight here.

  • If I don't want to select anything in criteria 1 (or 2):
I'll try to find something along the lines of "If B6 = "" Then only filter B7", or "Only filter B6; B7; BX if =/= "" ".

Thank you for your help so far!

With love ♥,
 
Upvote 0
YourBroLucas said:
  • .ShowAllData:
  • Non-existent combination:
  • If I don't want to select anything in criteria 1 (or 2):
Click to expand...

Try:

VBA Code: 
Sub filterdata()
  Dim shtGen As Worksheet, shtTotal As Worksheet
  Dim cDir, cType
  Dim lr As Long
 
  Set shtGen = ActiveWorkbook.Worksheets("Tab_Général")
  Set shtTotal = ActiveWorkbook.Worksheets("RECAP_TOTAL")
  
  If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  lr = shtGen.Range("C" & Rows.Count).End(3).Row
  shtTotal.Range("A16:P" & Rows.Count).ClearContents
  cDir = shtTotal.Range("B6").Value
  cType = shtTotal.Range("B7").Value
  
  With shtGen.Range("A16:P" & lr)
    If cDir <> "" Then .AutoFilter 3, cDir Else .AutoFilter 3, "*"
    If cType <> "" Then .AutoFilter 14, cType Else .AutoFilter 14, "*"
  End With
  
  If shtGen.Range("C" & Rows.Count).End(3).Row > 16 Then
    shtGen.AutoFilter.Range.Copy shtTotal.Range("A16")
    shtGen.ShowAllData
  Else
    MsgBox "Non-existent combination"
  End If
End Sub
 
Upvote 0
Hi Dante,

I tried this solution and it works in regard to filtering data.

Though some features don't seem to work:

  • Both shtGen.ShowAllData return a Runtime error 1004, though all online threads talk about password and sheet protection, which isn't my case. I've tried multiple solutions from other threads unsuccessfully.

  • Since ShowAllData doesn't work, the table in shtGen hides whatever isn't filtered during the macro's first run. As such, it causes shtGen.AutoFilter.Range.Copy shtTotal.Range("A16") to return a runtime error 91.

  • The MsgBox "Non-existent combination" don't show up when I try to filter combinations that I know for a fact don't exist. Additionally, a no-result combination causes the entire shtGen table to appear in shtTotal.

It's killing me that something seemingly as simple as ShowAllData causes me so much trouble...

As of now, the macro runs well the first time if I turn off both ShowAllData, but I have to unhide all rows manually or it won't work another time.

In most attempts, the entire shtGen table ends up being pasted (unfiltered) in shtTotal while shtGen itself remains filtered which it should not 😭

Thank you for your help though!

VBA Code: 
' Criteria
    Dim StartCell As Range
    Dim cDir As Range
    Dim cType As Range
    Dim lr As Long
    Set StartCell = shtGen.Range("A16")
    Set cDir = shtTotal.Range("B6")
    Set cType = shtTotal.Range("B7")
    lr = shtGen.Range("C" & Rows.Count).End(3).Row
    
'
' Filter
  With shtGen.Range("A16:P" & lr)
    If cDir <> "" Then .AutoFilter 3, cDir Else .AutoFilter 3, "*"
    If cType <> "" Then .AutoFilter 14, cType Else .AutoFilter 14, "*"
  End With
  
  If shtGen.Range("C" & Rows.Count).End(3).Row > 16 Then
    shtGen.AutoFilter.Range.Copy shtTotal.Range("A15")
    ' Runtime error 1004 here:
    shtGen.ShowAllData
  Else
    MsgBox "Non-existent combination"
  End If

' Return to normal in shtGen
    Application.CutCopyMode = False
    ' Runtime error 1004 here:
    shtGen.ShowAllData
    shtGen.AutoFilterMode = False

End Sub
 
Upvote 0
I din't copy the entire formula, my mistake, here is the macro as of now (see below)

In addition, I must stress that the macro returning errors is very inconsistent and that the grasp of the reasons behind such issues is now far beyond my understanding.
For e.g. after the 28th attempt, shtGen.ShowAllData showed all data both in shtGen and shtTotal (?!?), meaning it unfiltered the shtTotal properly filtered data.

VBA Code: 
' Criteria
    Dim StartCell As Range
    Dim cDir As Range
    Dim cType As Range
    Dim lr As Long
    Set StartCell = shtGen.Range("A16")
    Set cDir = shtTotal.Range("B6")
    Set cType = shtTotal.Range("B7")
  
'
' Filter
  If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  lr = shtGen.Range("C" & Rows.Count).End(3).Row
  shtTotal.Range("A16:P" & Rows.Count).ClearContents
  cDir = shtTotal.Range("B6").Value
  cType = shtTotal.Range("B7").Value

  With shtGen.Range("A16:P" & lr)
    If cDir <> "" Then .AutoFilter 3, cDir Else .AutoFilter 3, "*"
    If cType <> "" Then .AutoFilter 14, cType Else .AutoFilter 14, "*"
  End With
 
  If shtGen.Range("C" & Rows.Count).End(3).Row > 16 Then
    shtGen.AutoFilter.Range.Copy shtTotal.Range("A15")
    ' Runtime error 1004 here:
    shtGen.ShowAllData
  Else
    MsgBox "Non-existent combination"
  End If

' Return to normal in shtGen
    Application.CutCopyMode = False
    ' Runtime error 1004 here:
    shtGen.ShowAllData
    shtGen.AutoFilterMode = False

End Sub
 
Upvote 0
Try:

VBA Code: 
Sub filterdata()
  Dim shtGen As Worksheet, shtTotal As Worksheet
  Dim cDir, cType
  Dim lr As Long
 
  Set shtGen = ActiveWorkbook.Worksheets("Tab_Général")
  Set shtTotal = ActiveWorkbook.Worksheets("RECAP_TOTAL")
  
  If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  lr = shtGen.Range("C" & Rows.Count).End(3).Row
  shtTotal.Range("A16:P" & Rows.Count).ClearContents
  cDir = shtTotal.Range("B6").Value
  cType = shtTotal.Range("B7").Value
  
  With shtGen.Range("A16:P" & lr)
    If cDir <> "" Then .AutoFilter 3, cDir Else .AutoFilter 3, "*"
    If cType <> "" Then .AutoFilter 14, cType Else .AutoFilter 14, "*"
  End With
  
  If shtGen.Range("C" & Rows.Count).End(3).Row > 16 Then
    shtGen.AutoFilter.Range.Copy shtTotal.Range("A16")
    If shtGen.AutoFilterMode Then shtGen.AutoFilterMode = False
  Else
    MsgBox "Non-existent combination"
  End If
End Sub

But don't modify the macro, you are changing several lines and then I won't be able to help you.

Try my macro. If you have any problems, you should put here the data you are testing with, so that I can check how your data is. Since my tests are all satisfactory.

----------------
NOTE XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hi Dante,

So I tried again and the same runtime error 91 (undefined block With or object variable).

I can't select all the sheet so here's a part of the table in shtGen.

I did not change anything from your last macro.

May it be due to my computer memory as someone suggested? Or the Excel version?

Sincerely,

Dropdown-Filter file.xlsm
ABCDEFGHIJKLMNOP
15N°Date créationDirectionBureauPrestataireFiche comBDC/EJ AE antérieurs AE CP Restant dûSFJustif. ÉcartType..2
16101/01/2022DICOMBCEPCan't be disclosed151104827612,00 €1,00 €2,00 €- 1,00 €Evénements
17202/01/2022DICOMBCEPCan't be disclosed140568449412,00 €1,00 €2,00 €- 1,00 €Evénements
18303/01/2022DICOMBCEPCan't be disclosed8968057140568564212,00 €1,00 €2,00 €- 1,00 €Evénements
19404/01/2022DICOMBCEPCan't be disclosed9061815151112294712,00 €1,00 €2,00 €- 1,00 €Outils et goodies
20505/01/2022DICOMBCEPCan't be disclosed9115053140570406612,00 €1,00 €2,00 €- 1,00 €Evénements
21606/01/2022DICOMBADSCCan't be disclosed12,00 €1,00 €2,00 €- 1,00 €RS
22707/01/2022DICOMBCEPCan't be disclosed9130454151113387812,00 €1,00 €2,00 €- 1,00 €Campagnes
23808/01/2022DICOMBAVPCan't be disclosed9135460140571145612,00 €1,00 €2,00 €- 1,00 €Audiovisuel
24909/01/2022DICOMBAVPCan't be disclosed913529012,00 €1,00 €2,00 €- 1,00 €Audiovisuel
251010/01/2022DICOMBADSCCan't be disclosed9153409140572997512,00 €1,00 €2,00 €- 1,00 €Renouvellement
261111/01/2022DICOMBADSCCan't be disclosed9153720140572370412,00 €1,00 €2,00 €- 1,00 €Hébergement
271212/01/2022DICOMBCGSPCan't be disclosed9165660140571606212,00 €1,00 €2,00 €- 1,00 €Graphisme
281313/01/2022DICOMBAVPCan't be disclosed919124414057233551,00 €2,00 €- 1,00 €Audiovisuel
291414/01/2022DAPDAPCan't be disclosed919551815110979311,00 €2,00 €- 1,00 €
301515/01/2022DSJDSJCan't be disclosed920185115111102071,00 €2,00 €- 1,00 €
311616/01/2022DICOMBCEPCan't be disclosed919744614057235141,00 €2,00 €- 1,00 €Outils et goodies
321717/01/2022DSJDSJCan't be disclosed920192514056974221,00 €2,00 €- 1,00 €
331818/01/2022DSJDSJCan't be disclosed920183715111102051,00 €2,00 €- 1,00 €
341919/01/2022DSJDSJCan't be disclosed920190914056974261,00 €2,00 €- 1,00 €
352020/01/2022DSJDSJCan't be disclosed920194414056975291,00 €2,00 €- 1,00 €
362121/01/2022DPJJDPJJCan't be disclosed920198214057008941,00 €2,00 €- 1,00 € Audiovisuel
372222/01/2022DPJJDPJJCan't be disclosed920707215111009911,00 €2,00 €- 1,00 € Campagnes
382323/01/2022DPJJDPJJCan't be disclosed921542115111093921,00 €2,00 €- 1,00 € Evénements
392424/01/2022DPJJDPJJCan't be disclosed92225651511154481,00 €2,00 €- 1,00 € Edition
402525/01/2022DICOMBAPECan't be disclosed922244015111584491,00 €2,00 €- 1,00 €Audiovisuel
412626/01/2022DICOMBCEPCan't be disclosed923284714057256461,00 €2,00 €- 1,00 €Outils et goodies
422727/01/2022DPJJDPJJCan't be disclosed923346515111208401,00 €2,00 €- 1,00 € Evénements
432828/01/2022DICOMBAPECan't be disclosed923615415111731231,00 €2,00 €- 1,00 €Fonctionnement
442929/01/2022DSJDSJCan't be disclosed924035315111285291,00 €2,00 €- 1,00 €
453030/01/2022DSJDSJCan't be disclosed923896014057113541,00 €2,00 €- 1,00 €
463131/01/2022DICOMBCEPCan't be disclosed924994715111494351,00 €2,00 €- 1,00 €Outils et goodies
473201/02/2022DSJDSJCan't be disclosed925279515111413691,00 €2,00 €- 1,00 €
483302/02/2022DICOMBCEPCan't be disclosed925110314057279621,00 €2,00 €- 1,00 €Evénements
493403/02/2022DICOMBCEPCan't be disclosed925699914057267641,00 €2,00 €- 1,00 €Outils et goodies
503504/02/2022DICOMBCEPCan't be disclosed926550314057339151,00 €2,00 €- 1,00 €CampagnesEn attente
513605/02/2022DICOMBCEPCan't be disclosed926366814057361011,00 €2,00 €- 1,00 €Outils et goodies
523706/02/2022DICOMBAPECan't be disclosed927061015111828181,00 €2,00 €- 1,00 €Audiovisuel
533807/02/2022DICOMBAPECan't be disclosed92706371,00 €2,00 €- 1,00 €Audiovisuel
543908/02/2022DPJJDPJJCan't be disclosed927235515111603201,00 €2,00 €- 1,00 € Evénements
554009/02/2022DICOMBADSCCan't be disclosed927035414057332101,00 €2,00 €- 1,00 €Renouvellement
564110/02/2022DICOMBCEPCan't be disclosed927239014057429821,00 €2,00 €- 1,00 €Evénements
574211/02/2022DICOMBCGSPCan't be disclosed15111853821,00 €2,00 €- 1,00 €Impression
584312/02/2022DICOMBAPECan't be disclosed927532215111832081,00 €2,00 €- 1,00 €Audiovisuel
594413/02/2022DICOMBAPECan't be disclosed927788515111852591,00 €2,00 €- 1,00 €Audiovisuel
604514/02/2022DICOMBCGSPCan't be disclosed928110014057503931,00 €2,00 €- 1,00 €Impression
614615/02/2022DICOMDACGCan't be disclosed928069114057477841,00 €2,00 €- 1,00 €Outils et goodies
624716/02/2022DICOMBAPECan't be disclosed928649514057518891,00 €2,00 €- 1,00 €Fonctionnement
634817/02/2022PVAMPVAMCan't be disclosed928642815111682891,00 €2,00 €- 1,00 €
644918/02/2022DICOMBAVPCan't be disclosed929197915112050111,00 €2,00 €- 1,00 €Audiovisuel
655019/02/2022DAPDAPCan't be disclosed929244615111690721,00 €2,00 €- 1,00 €
665120/02/2022DICOMBAVPCan't be disclosed929115415112079161,00 €2,00 €- 1,00 €Audiovisuel
675221/02/2022DSJDSJCan't be disclosed929369815111740131,00 €2,00 €- 1,00 €
685322/02/2022DSJDSJCan't be disclosed929468415111740121,00 €2,00 €- 1,00 €
695423/02/2022DICOMBAVPCan't be disclosed929429115112053831,00 €2,00 €- 1,00 €Evénements
705524/02/2022DICOMBAVPCan't be disclosed929992715112053031,00 €2,00 €- 1,00 €Audiovisuel
715625/02/2022DICOMBAVPCan't be disclosed930332914057629581,00 €2,00 €- 1,00 €Audiovisuel
725726/02/2022DSJDSJCan't be disclosed930450215111828251,00 €2,00 €- 1,00 €
735827/02/2022DAPDAPCan't be disclosed930522614057429891,00 €2,00 €- 1,00 €
745928/02/2022DAPDAPCan't be disclosed930906914057445801,00 €2,00 €- 1,00 €
756001/03/2022DICOMBAVPCan't be disclosed930531714058302751,00 €2,00 €- 1,00 €Audiovisuel
766102/03/2022DICOMBCEPCan't be disclosed930301614057400701,00 €2,00 €- 1,00 €Outils et goodies
776203/03/2022DPJJDPJJCan't be disclosed931177815111868491,00 €2,00 €- 1,00 € Campagnes
786304/03/2022DICOMBCEPCan't be disclosed931461615112517341,00 €2,00 €- 1,00 €Campagnes
796405/03/2022DICOMBADSCCan't be disclosed931376314057646891,00 €2,00 €- 1,00 €Renouvellement
806506/03/2022DICOMBADSCCan't be disclosed931377714057630271,00 €2,00 €- 1,00 €Renouvellement
816607/03/2022DAPDAPCan't be disclosed932034714057480751,00 €2,00 €- 1,00 €
826708/03/2022DICOMBAVPCan't be disclosed931995414057879181,00 €2,00 €- 1,00 €Audiovisuel
836809/03/2022DICOMBAVPCan't be disclosed932192914057722481,00 €2,00 €- 1,00 €Audiovisuel
846910/03/2022DAPDAPCan't be disclosed932590314057572961,00 €2,00 €- 1,00 €
857011/03/2022DAPDAPCan't be disclosed932727115112055651,00 €2,00 €- 1,00 €
867112/03/2022DICOMBCGSPCan't be disclosed932721515112590101,00 €2,00 €- 1,00 €Impression
877213/03/2022DICOMEgalité FHCan't be disclosed932752015112691881,00 €2,00 €- 1,00 €Egalité FH
887314/03/2022DPJJDPJJCan't be disclosed932287515112050101,00 €2,00 €- 1,00 € Evénements
Tab_Général
Cell Formulas
RangeFormula
A16:A88A16=ROW($A16)-15
K16:K88K16=I16-J16
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K15:K172Cell Value=0textNO
K15:K172,I173:I1048576Cell Value<0textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Offset on merged cells dynamically after dynamic row insertion
Replies
0
Views
489
YourBroLucas
Y
J
  • Question Question
How can I unite these two scripts to send emails with single or multiple attachments to a list of contacts?
Replies
8
Views
1K
JorgeMartinez
J
P
  • Question Question
VBA Slow code, please help optimize
Replies
1
Views
2K
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Help with Find command - Match names on multiple tabs
Replies
0
Views
854
Stu96
S
Johnny Thunder
  • Question Question
VBA Help - How to update Range based on Filtered Criteria - Excel 2016
Replies
1
Views
825
BobUmlas
B

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top