VBA Check for cell words

JoeExcelHelp
5StarLounger
Posts: 1146
Joined: 22 Jul 2013, 18:29

VBA Check for cell words

Post by 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

User avatar
HansV
Administrator
Posts: 72233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Check for cell words

Post by 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".
Regards,
Hans

JoeExcelHelp
5StarLounger
Posts: 1146
Joined: 22 Jul 2013, 18:29

Re: VBA Check for cell words

Post by 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