Countif Line

[JoeExcelHelp]

Trying to get this 1 line to work.. been at it for an hour :(

Code: Select all

sFile & "C17,""=""&sFile & "Q17," & _

Code: Select all

Sub ImportOrientation()
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
Dim rgS As Range

    Application.ScreenUpdating = False
    
    sPath = "\\kewntfile01\sharedfile\Operations Planning\__Dashboards by RP\Airport Training_Class_Rosters\SSR Source.xlsm"
    If sPath = "False" Then Exit Sub
    Set wbD = ThisWorkbook
    Set shD = wbD.Sheets("Orientation")
    Set rgD = Intersect(shD.Range("A4").CurrentRegion, shD.Range("A4").CurrentRegion.Offset(3, 1))
    Workbooks.Open sPath, UpdateLinks:=False
    Set wbS = ActiveWorkbook
    Set shS = wbS.Sheets(1)
    Set rgS = shS.Range("A1").CurrentRegion
    sFile = "'[" & Split(sPath, "\")(UBound(Split(sPath, "\"))) & "]" & shS.Name & "'!"
    rgD.FormulaR1C1 = _
        "=COUNTIFS(" & sFile & "C7,""=""&R5C," & _
        sFile & "C8,""=""&R6C," & _
        sFile & "C9,""=""&RC1," & _
        sFile & "C2,""=""&R4C," & _
        sFile & "C17,""=""&sFile & "Q17," & _
        sFile & "C4,""<>""&""""," & _
        sFile & "C6,""<>""&""BP"")"
    rgD.Value = rgD.Value
    wbS.Close False
    Application.ScreenUpdating = True
End Sub

[Rudi]

I have no idea if this is correct or not, but it compiles OK based on the changes I made...
The other thing I think is contributing to the error is that you cannot mix A1 notation and R1C1 notation in the same formula.

Code: Select all

rgD.FormulaR1C1 = "=COUNTIFS(" & _
        sFile & "C7,""=""&R5C," & _
        sFile & "C8,""=""&R6C," & _
        sFile & "C9,""=""&RC1," & _
        sFile & "C2,""=""&R4C," & _
        sFile & "C17,""=""" & _
        sFile & "Q17," & _
        sFile & "C4,""<>"" & """"," & _
        sFile & "C6,""<>"" & ""BP"")"

[HansV]

What is the purpose of Q17? It is not R1C1 notation...