Writing Countif and xlookup formulas into VBA Macro

This week I have started learning to use VBA macros so I apologies in advance if I get the terminology wrong.

I have recorded my macro in Excel and it "works' however it takes a long time to run and on occasion crash. I think the issue might be that I have a number of IfCountifs and Xlookup formulas in the macro.

Is there a more efficient way to write the following formulas:

    Dim lr As Long
    lr = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    
        Range("H7").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(COUNTIFS('Sheet1'!C[-7],Sheet2!RC[-7],'Sheet1!C[1],""*Text*""), ""Text"", ""Text"")"
        Range("H7").Select
        Selection.AutoFill Destination:=Range("H7:H" & lr)
    
        Range("K7").Select
        ActiveCell.Formula2R1C1 = _
            "=XLOOKUP(1,('Sheet1'!C[-10]=Sheet2!RC[-10])*('Sheet1!C[-1]=""Text""), 'Sheet1'!C[1], """")"
        Range("K7").AutoFill Destination:=Range("K7:K" & lr)

In Excel the formulas look like this:

    =IF(COUNTIFS('Sheet1'!A:A,Data!A7,'Sheet1'!I:I,"*Text*"), "Text", "Text")
    =XLOOKUP(1,('Sheet1'!A:A=Sheet2!A7)*('Sheet1'!J:J="Text"), 'Sheet1'!L:L, "")

The macro 'works' but it is really slow and occasionally crashes.

Write Formulas Using VBA

• You should avoid using entire columns to look up data.
• You should consider putting the data into Excel (structured) tables. Then formulas with structured references would ensure it to be dynamic and all this VBA business wouldn't be necessary.

<!-- language: lang-vb -->

Sub WriteFormulas()
Const SRC_SHEET As String = "Sheet1"
Const SRC_FIRST_ROW As Long = 2 ' adjust!!!
Const DST_SHEET As String = "Sheet2"
Const DST_FIRST_ROW As Long = 7
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
' Source
Dim srg As Range, sLastRow As Long
With wb.Sheets(SRC_SHEET)
sLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set srg = .Range(.Cells(SRC_FIRST_ROW, "A"), .Cells(sLastRow, "A"))
End With
' Sheet
Dim sSheet As String: sSheet = "'" & SRC_SHEET & "'!"
' Lookup
Dim SL1 As String: SL1 = sSheet & srg.Address
Dim SL2 As String: SL2 = sSheet & srg.EntireRow.Columns("I").Address
Dim SL3 As String: SL3 = sSheet & srg.EntireRow.Columns("J").Address
' Return
Dim SR3 As String: SR3 = sSheet & srg.EntireRow.Columns("L").Address
' Destination
Dim drg As Range, dLastRow As Long
With wb.Sheets(DST_SHEET)
dLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set drg = .Range(.Cells(DST_FIRST_ROW, "A"), .Cells(dLastRow, "A"))
End With
' lookup
Dim DL1 As String: DL1 = drg.Cells(1).Address(0, 0)
' Formulas
' =IF(COUNTIFS('Sheet1'!A:A,A7,'Sheet1'!I:I,"*Text*"),"Yes", "No")
With drg.Columns("H")
.Formula = "=IF(COUNTIFS(" & SL1 & "," & DL1 & "," & SL2 _
& ",""*Text*""),""Yes"",""No"")"
End With
' =XLOOKUP(1,(Sheet1!A:A=A7)*(Sheet1!J:J="Text"),Sheet1!L:L,"")
With drg.Columns("K")
.Formula2 = "=XLOOKUP(1,(" & SL1 & "=" & DL1 & ")*(" _
& SL3 & "=""Text"")," & SR3 & ","""")"
End With
' Inform.
MsgBox "Formulas written.", vbInformation
End Sub