VBA - Excel - How to write a formula in a cell instead of values using list objects

Question

I'm struggling with VBA and couldn't find a solution on internet.

This is what I'm trying to do. I have an excel file with two sheets First sheet is an extract from a Database (with many columns, an particulary one called "country"). It is formated as a ListObject Second sheet is a Calculator that is counting the number of occurence of every country in the other sheet. It is also formated as a list object

For Example at the end I'll get: France: 10 USA: 25 Italia: 30

I found how to calculate this using the Application.WorksheetFunction.CountIf function.

Now my question is: Is it possible to write the formula in the cell, instead of writing the result ? The objectif is to generate a sheet filled with formulas, so that when I change things in the Database sheet, then it will automatically recalculate my results.

This is my current code: It works, but I'd like to write a formula in the cell, instead of the result. It there an easyway to do this ?

Thanks a lof for your help ! Regards, J.

Dim i As Long

Dim CountryName As Variant
    
Dim KPI_LO As ListObject
Dim REVIEWEDDATA_LO As ListObject

'This is the list object with the database information
Set REVIEWEDDATA_LO = Worksheets(REVIEWEDDATA_SHEET_NAME).ListObjects(REVIEWEDDATA_LISTOBJECT_NAME)
'This is the list object where I'll store my results
Set KPI_LO = Worksheets(WASPKPI_SHEET_NAME).ListObjects(WASPKPI_LISTOBJECT_NAME)
 
'loop through each country column in the result sheet
For i = LBound(GetCountriesList) To UBound(GetCountriesList)
        'Get the name of the first country to find
        CountryName = GetCountriesList(i)
        
        'Count the number of occurence of this country in the column called COL_COUNTRY in the revieweddata List Object (in my database sheet).
        'This is what I'm trying to replace. I want to write a formula that do this calculation in the cell, instead of the result.
        KPI_LO.ListColumns(CountryName).DataBodyRange.Rows(1) = Application.WorksheetFunction.CountIf(REVIEWEDDATA_LO.ListColumns(COL_COUNTRY).DataBodyRange, CountryName)
Next i

Answer 1

if you want to see the formula in a cell, then you can create a function and then just call that and pass through your values.

For example, create a module:

Function fn_Multiply(x As Double, y As Double) As Double

Dim z As Double
z = x * y

fn_Multiply = z

End Function

And use it in excel as a formula like this:

=fn_Multiply(A2,B2)

enter image description here