VBA Vlookup to Another Workbook using pathname & Variables

[ABCDH]

I am new to VBA. The code below works, except for two issues I cannot solve.

(1) When I run macro, I have to manually select the path to the file (ProviderCounts) in vlookup. If I try Workbooks.Open; then the file opens, but stays on that file and resets the macro to the beginning.

(2) Within the Vlookup, I want to replace cell reference C36 with a variable. This is part of a larger macro and the row number may change. Ie. replace C36 with variable X. X = Range("C" & RowNumber + 3).Value

Variable X (or C36) is a DATE. I've read that declaring variable X as Date does not work. I've been unsuccessful declaring as long or integer.

Any help is much appreciated. Thank you!

Sub MembershipCount()
Dim CountRows As Long, RowNumber As Long

'            Workbooks.Open ("S:\A\Provider counts\ProviderCounts.xlsx")

With Sheets("Income Statement")
    CountRows = .Range("A" & Rows.Count).End(xlUp).Row
    For RowNumber = CountRows To 1 Step -1
        If Cells(RowNumber, 1) = "Net Income" Then
            Range("C" & RowNumber + 4).Formula = "=Vlookup(C36, 'S:\A\Provider Counts\[ProviderCounts.xlsx]Sheet1'!$A:$B, 2, False)"
        End If
    Next RowNumber
End With
End Sub