VBA to create formula based on another variable

Question

I am trying to create the formula below using vba in excel

=SUM(COUNTIF(E7,"Vac")+ COUNTIF(E7,"LWOP")+COUNTIF(R7,"Vac")+ COUNTIF(R7,"LWOP"))

But E7 and R7 will change based on another variable called rCell.address

Below is the code that I have within the macro and it is giving an error:

Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ", "VAC"" & "))"

The current macro is:

Sub Find()

Dim strdate As String
Dim rCell As Range
Dim lReply As Long

With Worksheets("Sheet1")
strdate = .Range("a1").Value
End With

If strdate = "False" Then Exit Sub
strdate = Format(strdate, "Short Date")

On Error Resume Next

    Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

On Error GoTo 0

If rCell Is Nothing Then
    lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
    If lReply = vbYes Then Run "FindDate":
End If

Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ",VAC" & "))"
End Sub

Answer 1

I am assuming that your error appears on this line of code: Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ",VAC" & "))".

You should change that line to

Range("a8").Formula = "=SUM(COUNTIF(" & rCell.Address & "," & Chr(34) & "Vac" & Chr(34) & ")+ COUNTIF(" & rCell.Address & "," & Chr(34) & "LWOP" & Chr(34) & ")+COUNTIF(" & rCell.Offset(0, 13).Address & "," & Chr(34) & "Vac" & Chr(34) & ")+ COUNTIF(" & rCell.Offset(0, 13).Address & "," & Chr(34) & "LWOP" & Chr(34) & "))"

Updated with better answer thanks to KS Sheon

Range("a8").Formula = "=SUM(COUNTIF(" & rCell.Address & ",""Vac"")+ COUNTIF(" & rCell.Address & ",""LWOP"")+COUNTIF(" & rCell.Offset(0, 13).Address & ",""Vac"")+ COUNTIF(" & rCell.Offset(0, 13).Address & ",""LWOP""))"