Adding to existing condition

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

Adding to existing condition

Post by JoeExcelHelp »

Im trying to add a 2nd "Orientation" sheet condition the code below but cant seem to get it to work

2nd Condition

Code: Select all

Set rgD = Intersect(shD.Range("N1").CurrentRegion, shD.Range("N1").CurrentRegion.Offset(1, 3))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&RC15," & _
        sFile & "C6,""=""&RC16," & _
        sFile & "C8,""=""&RC14," & _
        sFile & "C2,""=""&R6C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""Business Partner""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value
My attempt

Code: Select all

Sub UpdateActiveInactiveOrientation()
    Dim sPath As String
    Dim sFile As String
    Dim wbD As Workbook
    Dim shD As Worksheet
    Dim rgD As Range
    Dim wbS As Workbook
    Dim shS As Worksheet

    Application.ScreenUpdating = False
    
    sPath = "\\jfkfile01_new\home\GG70397\SSR Source.xlsm"
    Set wbS = Workbooks.Open(Filename:=sPath, UpdateLinks:=False)
    Set shS = wbS.Sheets(1)
    sFile = "'[SSR Source.xlsm]" & shS.Name & "'!"

    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Actuals")
    Set rgD = Intersect(shD.Range("A4").CurrentRegion, shD.Range("A4").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C9," & _
        sFile & "C1,""=""&RC1," & _
        sFile & "C4,""=""&R6C," & _
        sFile & "C2,""=""&R5C," & _
        sFile & "C5,""<>""&""S"")"
    rgD.Value = rgD.Value

    Set rgD = Intersect(shD.Range("G4").CurrentRegion, shD.Range("G4").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C11," & _
        sFile & "C1,""=""&RC7," & _
        sFile & "C4,""=""&R6C," & _
        sFile & "C2,""=""&R5C," & _
        sFile & "C5,""<>""&""S"")"
    rgD.Value = rgD.Value
    
    Set rgD = Intersect(shD.Range("M4").CurrentRegion, shD.Range("M4").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C9," & _
        sFile & "C1,""=""&RC13," & _
        sFile & "C4,""=""&R6C," & _
        sFile & "C2,""=""&R5C," & _
        sFile & "C5,""=""&""S"")"
    rgD.Value = rgD.Value

    
    Set shS = wbS.Sheets(2)
    sFile = "'[SSR Source.xlsm]" & shS.Name & "'!"
    
    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Orientation")
    Set rgD = Intersect(shD.Range("A6").CurrentRegion, shD.Range("A6").CurrentRegion.Offset(1, 3))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&RC2," & _
        sFile & "C6,""=""&RC3," & _
        sFile & "C8,""=""&RC1," & _
        sFile & "C2,""=""&R6C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""Business Partner""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value

    Set rgD = Intersect(shD.Range("N1").CurrentRegion, shD.Range("N1").CurrentRegion.Offset(1, 3))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&RC15," & _
        sFile & "C6,""=""&RC16," & _
        sFile & "C8,""=""&RC14," & _
        sFile & "C2,""=""&R6C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""Business Partner""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value

    wbS.Close False
    Application.ScreenUpdating = True
End Sub
Top
JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Adding to existing condition

Post by JoeExcelHelp »

Sorry this line should read

Set rgD = Intersect(shD.Range("N6").CurrentRegion, shD.Range("N6").CurrentRegion.Offset(1, 3))
Not
Set rgD = Intersect(shD.Range("N1").CurrentRegion, shD.Range("N1").CurrentRegion.Offset(1, 3))

But that still didnt work
Top
User avatar
HansV
Administrator
Posts: 78631
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding to existing condition

Post by HansV »

1) You use .Offset(3, 1) in the original code, but .Offset(1, 3) in the added code. Is that intentional?

2) All your SUMIFS operate on a single cell only, instead of on a range of cells. Is that intentional?
Best wishes,
Hans
Top
JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Adding to existing condition

Post by JoeExcelHelp »

Yes Hans all intentional.. I got it to run like this.. Thanks for the attention

Code: Select all

Sub UpdateActiveInactiveOrientation()
        Dim sPath As String
    Dim sFile As String
    Dim wbD As Workbook
    Dim shD As Worksheet
    Dim rgD As Range
    Dim wbS As Workbook
    Dim shS As Worksheet

    Application.ScreenUpdating = False
    
    sPath = "\\jfkfile01_new\home\GG70397\SSR Source.xlsm"
    Set wbS = Workbooks.Open(Filename:=sPath, UpdateLinks:=False)
    Set shS = wbS.Sheets(1)
    sFile = "'[SSR Source.xlsm]" & shS.Name & "'!"

    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Actuals")
    Set rgD = Intersect(shD.Range("A1").CurrentRegion, shD.Range("A1").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C9," & _
        sFile & "C1,""=""&RC1," & _
        sFile & "C4,""=""&R3C," & _
        sFile & "C2,""=""&R2C," & _
        sFile & "C5,""<>""&""S"")"
    rgD.Value = rgD.Value

    Set rgD = Intersect(shD.Range("G1").CurrentRegion, shD.Range("G1").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C11," & _
        sFile & "C1,""=""&RC7," & _
        sFile & "C4,""=""&R3C," & _
        sFile & "C2,""=""&R2C," & _
        sFile & "C5,""<>""&""S"")"
    rgD.Value = rgD.Value
    
    Set rgD = Intersect(shD.Range("M1").CurrentRegion, shD.Range("M1").CurrentRegion.Offset(3, 1))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C9," & _
        sFile & "C1,""=""&RC13," & _
        sFile & "C4,""=""&R3C," & _
        sFile & "C2,""=""&R2C," & _
        sFile & "C5,""=""&""S"")"
    rgD.Value = rgD.Value

    Set shD = wbD.Sheets("Orientation")
    Set rgD = Intersect(shD.Range("A1").CurrentRegion, shD.Range("A1").CurrentRegion.Offset(1, 3))
    Set shS = wbS.Sheets(2)
    sFile = "'[SSR Source.xlsm]" & shS.Name & "'!"
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&RC2," & _
        sFile & "C6,""=""&RC3," & _
        sFile & "C8,""=""&RC1," & _
        sFile & "C2,""=""&R1C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""Business Partner""," & _
        sFile & "C4,""<>""&""Internal""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value
    
    Set rgD = Intersect(shD.Range("P1").CurrentRegion, shD.Range("P1").CurrentRegion.Offset(1, 3))
    rgD.FormulaR1C1 = _
        "=SUMIFS(" & sFile & "C16," & _
        sFile & "C5,""=""&RC17," & _
        sFile & "C6,""=""&RC18," & _
        sFile & "C8,""=""&RC16," & _
        sFile & "C2,""=""&R1C," & _
        sFile & "C1,""<>""&""""," & _
        sFile & "C4,""<>""&""Business Partner""," & _
        sFile & "C4,""<>""&""Internal""," & _
        sFile & "C4,""<>""&""BP"")"
    rgD.Value = rgD.Value

    wbS.Close False
    Application.ScreenUpdating = True
End Sub
Top

Return to “Excel and other spreadsheet applications”