Syntax error - Excel formulas in VBA code

[Damian37]

Hi Kevin,
Per our discussion, I would like to add the Excel formulas I’m currently using in Excel to my VBA code. The file I’m working on is here: All Items- CAPA Tracker - Macro Ver 4 with calculated columns.xlsm
Here are the formulas I've placed within the Excel worksheet. Please let me know if you have any questions. Thanks so much!

Retrieving Due Date from two columns (Original Due Date & Revised Due Date) - =IF(S4<>"", S4, R4)
Retrieving Date Closed. Some cells may not have a value yet. - =IF(ISBLANK(W4),"",TEXT(W4,"mm/dd/yyyy"))
Calculating # of days from Date Closed against Due Date. - =IF(ISBLANK(W4), AP4-TODAY(), AP4-AQ4)
Designating if project was "Closed On Time", "Overdue", or "In Progress". - =IF(O4= "In Progress", "In Progress", IF(AR4>0, "Completed On Time", IF(AR4<0, "Overdue",IF(AR4=0, "Completed On Time"))))

D

 

[kevin9999]

Please see if the following gives you what you want. Link to the file for testing: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 5.xlsm

Option Explicit
Sub FilterMultipleCriteria_V5()
    Dim ws1 As Worksheet, ws2 As Worksheet, criteriaArray
    
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
    
    'Filter, copy and add formulas
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 5, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            ws2.Range("A1").CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Range("A2")
            Dim LRow As Long
            LRow = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row
            With ws2
                .Range("AP2:AP" & LRow).Formula = "=IF(S2<>"""", S2, R2)"
                .Range("AQ2:AQ" & LRow).Formula = "=IF(ISBLANK(W2),"""",W2)"
                .Range("AR2:AR" & LRow).Formula = "=IF(ISBLANK(W2), AP2-TODAY(), AP2-AQ2)"
                .Range("AS2:AS" & LRow).Formula = "=IF(O2= ""In Progress"", ""In Progress"", IF(AR2>0, ""Completed On Time"", IF(AR2<0, ""Overdue"",IF(AR2=0, ""Completed On Time""))))"
                With .Range("AP2:AS" & LRow)
                    .Value = .Value
                End With
                .Columns("AP:AQ").NumberFormat = "mm/dd/yyyy"
            End With
        End If
    End With
    ws1.AutoFilter.ShowAllData
    
End Sub

 

[Damian37]

Please see if the following gives you what you want. Link to the file for testing: All Items- CAPA Tracker Fields Generic shareable - Marcro Unifinished Ver 5.xlsm

Option Explicit
Sub FilterMultipleCriteria_V5()
    Dim ws1 As Worksheet, ws2 As Worksheet, criteriaArray
  
    'Set the data range & criteria
    Set ws1 = Worksheets("Report Data")
    Set ws2 = Worksheets("Filtered Data")
    criteriaArray = Array("High", "Moderate-High")
  
    'Filter, copy and add formulas
    If ws1.AutoFilterMode Then ws1.AutoFilterMode = False
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 5, criteriaArray, 7
        If .SpecialCells(xlCellTypeVisible).Address <> .Rows(1).Address Then
            ws2.Range("A1").CurrentRegion.Offset(1).ClearContents
            .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Range("A2")
            Dim LRow As Long
            LRow = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row
            With ws2
                .Range("AP2:AP" & LRow).Formula = "=IF(S2<>"""", S2, R2)"
                .Range("AQ2:AQ" & LRow).Formula = "=IF(ISBLANK(W2),"""",W2)"
                .Range("AR2:AR" & LRow).Formula = "=IF(ISBLANK(W2), AP2-TODAY(), AP2-AQ2)"
                .Range("AS2:AS" & LRow).Formula = "=IF(O2= ""In Progress"", ""In Progress"", IF(AR2>0, ""Completed On Time"", IF(AR2<0, ""Overdue"",IF(AR2=0, ""Completed On Time""))))"
                With .Range("AP2:AS" & LRow)
                    .Value = .Value
                End With
                .Columns("AP:AQ").NumberFormat = "mm/dd/yyyy"
            End With
        End If
    End With
    ws1.AutoFilter.ShowAllData
  
End Sub

Hi Kevin,
I ran the updated query, however, some of the rows are coming out incorrectly. The highlighted line in the screenshot is showing how instead of calculating the # of days some rows are being populated with dates instead of numbers. Any ideas why this might be happening? Thanks.

 

[kevin9999]

I'm away from my home laptop for a while now, but I will get on to it.

 

[Damian37]

I'm away from my home laptop for a while now, but I will get on to it.

I appreciate it. The previous code is working great, and I can continue to use the calculated columns that I created. So, no need to rush. I do really appreciate all of your help. Thank you very much.

D

 

[kevin9999]

OK, not sure what happened there. When I ran the code in post #2 it ran just fine. Link to your supplied file with the macro: All Items- CAPA Tracker - Macro Ver 5 with calculated columns.xlsm
What it looked like for me:

All Items- CAPA Tracker - Macro Ver 5 with calculated columns.xlsm
	A	B	C	D	E	F									O			R	S				W				AA								AI						AO	AP	AQ	AR	AS
1	Project Name	Control ID	Observation ID (Project Name-Observation #)	Observation Description	Criticality	Vulnerability									CAPA State			Original Due Date	Revised Due Date				Date Closed				CAPA Start Date (Date Project Completed/Communicated)								Date Submitted						Updated Date	Due Date	Date Closed	# of Days to completion	Overdue/Completed on Time
2	Generic	21-999	21-999-03	Generic	High	High									Closed-Verified			30/06/2022	30/11/2022				12/1/2022 12:00 AM				12/22/2021 12:00 AM								5/12/2022						12/20/2023 3:49 PM	11/30/2022	12/01/2022	-1	Overdue
3	Generic	E.1-20-p-002	22-001-20-4	Generic	Moderate-High	Moderate									Closed-Verified			31/10/2023					9/21/2023 12:00 AM				12/16/2022 12:00 AM								21/09/2023						12/20/2023 2:54 PM	10/31/2023	09/21/2023	40	Completed On Time
4	Generic	A.1-05-b-015	22-002-05-02	Generic	Moderate-High	Moderate-High									Closed-Verified			30/11/2022					11/30/2022 12:00 AM				10/28/2022 12:00 AM								5/12/2022						12/20/2023 3:49 PM	11/30/2022	11/30/2022	0	Completed On Time
5	Generic	A.1-05-b-015	22-002-05-03	Generic	Moderate-High	Moderate-High									Closed-Verified			30/11/2022	30/01/2023				1/27/2023 12:00 AM				10/28/2022 12:00 AM								27/01/2023						12/11/2023 4:00 PM	01/30/2023	01/27/2023	3	Completed On Time
6	Generic	22-010	21-010-2	Generic	High	High									Closed-Verified			30/11/2022					11/30/2022 12:00 AM				8/8/2022 12:00 AM								5/12/2022						12/11/2023 4:00 PM	11/30/2022	11/30/2022	0	Completed On Time
7	Generic	22-026-13	22-026-13-1	Generic	Moderate-High	Moderate-High									Closed-Verified			31/03/2023	31/05/2023				5/22/2023 12:00 AM												29/03/2023						12/19/2023 5:40 PM	05/31/2023	05/22/2023	9	Completed On Time
8	Generic	22-026-13	22-026-13-1	Generic	Moderate-High	Moderate-High									Closed-Verified			30/11/2022					11/30/2022 12:00 AM				11/30/2022 12:00 AM														12/19/2023 5:40 PM	11/30/2022	11/30/2022	0	Completed On Time
9	Generic	22-026-41	22-026-41-1	Generic	Moderate-High	Moderate-High									Closed-Verified			31/01/2023					2/3/2023 12:00 AM				11/30/2022 12:00 AM								3/02/2023						12/19/2023 5:40 PM	01/31/2023	02/03/2023	-3	Overdue
10	Generic	22-026-41	22-026-41-2	Generic	Moderate-High	Moderate-High									Closed-Verified			30/11/2022					11/30/2022 12:00 AM				11/30/2022 12:00 AM														12/19/2023 5:40 PM	11/30/2022	11/30/2022	0	Completed On Time
11	Generic	22-026-41	22-026-41-2	Generic	Moderate-High	Moderate-High									Closed-Verified			31/01/2023					1/27/2023 12:00 AM				11/30/2022 12:00 AM								27/01/2023						12/19/2023 5:40 PM	01/31/2023	01/27/2023	4	Completed On Time
12	Generic	22-026-41	22-026-41-2	Generic	Moderate-High	Moderate-High									Closed-Verified			31/03/2023					1/31/2023 12:00 AM				11/30/2022 12:00 AM								31/01/2023						12/19/2023 5:40 PM	03/31/2023	01/31/2023	59	Completed On Time
Filtered Data