VBA Check for cell words

[JoeExcelHelp]

All,

I added this array line to the following and the code stopped working
Can you have more than 1 array line?

I'm basically checking is C6 contains the words "AO Supervisor" or CF Supervisor"

Code: Select all

sFile & "C6,{""AO Supervisor"",""CF Supervisor""}," & _

Code: Select all

With wsh.Range("G438:CD439")
                .FormulaR1C1 = _
                "=SUM(SUMIFS(" & sFile & "C12," & _
                sFile & "C8,""=""&RC2," & _
                sFile & "C7,""=""&R1C4," & _
                sFile & "C13,""=""&Month(R12C)," & _
                sFile & "C14,""=""&Year(R12C)," & _
                sFile & "C1,"">""&R5C2," & _
                sFile & "C3,""<>""," & _
                sFile & "C4,""<>""," & _
                sFile & "C11,""Yes""," & _
                sFile & "C9,""Permanent""," & _
                sFile & "C6,{""AO Supervisor"",""CF Supervisor""}," & _
                sFile & "C5,{""Rehire"",""External"",""Internal Requisition- Same Workgroup, Status, Station"",""Internal Requisition""}))"
                .Value = .Value
            End With

[HansV]

It is possible, but it won't do what you want. You'll get the sum of the rows where column F is "AO Supervisor" AND column E is "Rehire", plus those where column F is "CF Supervisor" AND column E is "External". The other options for column E will be ignored.
You'll have to add two SUMIFS together, one for "AO Supervisor" and another for "CF Supervisor".

[JoeExcelHelp]

Perfect Thank You Hans

Code: Select all

With wsh.Range("G438:CD439")
                .FormulaR1C1 = _
                "=SUM(SUMIFS(" & sFile & "C12," & _
                sFile & "C8,""=""&RC2," & _
                sFile & "C7,""=""&R1C4," & _
                sFile & "C13,""=""&Month(R12C)," & _
                sFile & "C14,""=""&Year(R12C)," & _
                sFile & "C1,"">""&R5C2," & _
                sFile & "C3,""<>""," & _
                sFile & "C4,""<>""," & _
                sFile & "C11,""Yes""," & _
                sFile & "C9,""Permanent""," & _
                sFile & "C6,""AO Supervisor""," & _
                sFile & "C5,{""Rehire"",""External"",""Internal Requisition- Same Workgroup, Status, Station"",""Internal Requisition""}))" & _
                "+SUM(SUMIFS(" & sFile & "C12," & _
                sFile & "C8,""=""&RC2," & _
                sFile & "C7,""=""&R1C4," & _
                sFile & "C13,""=""&Month(R12C)," & _
                sFile & "C14,""=""&Year(R12C)," & _
                sFile & "C1,"">""&R5C2," & _
                sFile & "C3,""<>""," & _
                sFile & "C4,""<>""," & _
                sFile & "C11,""Yes""," & _
                sFile & "C9,""Permanent""," & _
                sFile & "C6,""CF Supervisor""," & _
                sFile & "C5,{""Rehire"",""External"",""Internal Requisition- Same Workgroup, Status, Station"",""Internal Requisition""}))"
                .Value = .Value
            End With