My VBA code for creating a validation list in a workbook throws an error in method add formula1, but it works if I create it manually

This is an Excel VBA problem I can't solve.

I have a quite large and complex workbook, with many sheets. The main working sheet is named "Riassuntivo". In it, I create by code different tables depending on 2 cells that must be compiled by the user, one connected to the other. So, after the choice in the validation list in cell G5, another validation list is created in cell C4.

The validation list formula added in G4 is quite complex and it depends on a table in a different sheet called "filtri".

Anyway, if I insert the formula manually using the proper menu command, the procedure goes correctly to the end, and the list is visible.

Otherwise, using a VBA routine that should have to make dynamically the same operation, it stops for an error when applying the add method.

I report the part of the code here:

    Dim str2 As String, v1 As String, v2 As String, v3 As String, v4 As String
    Set rngSel = Range("A1:AA1")
    With rngSel
        Set rng = .Find(what:="mercato", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
        iCol = rng.Column
    End With
    Set rngSel = Range("A1:A120")
    With rngSel
        Set rng = .Find(what:="-", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
        iK = rng.Row
    End With
    Range("C1").End(xlToRight).Offset(0, 1).Select
    iFine = ActiveCell.Column
    v1 = ConvertToLetter(CLng(iCol))
    v2 = ConvertToLetter(CLng(iCol + 1))
    v3 = ConvertToLetter(CLng(iFine))
    v4 = ConvertToLetter(CLng(iFine + 1))
        [...]
        Sheets("Riassuntivo").Select
        Range("G4:K4").Select
        str = "=if($G$5="""";$AY$1;OFFSET(filtri!$" & v1 & "$1;2;MATCH($G$5;filtri!$" & v2 & "$1:$" & v4 & _
        "$1;0);COUNTIF(OFFSET(filtri!$" & v1 & "$1;2;MATCH($G$5;filtri!$" & v2 & "$1:$" & v4 & "$1;0);20);""<>-"")))"
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= str
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With

Unfortunately, because of the sheets involved, a minimal example is impossible to be inserted.

The debug.print of the string str gives the following result:

=IF($G$5="";$AY$1;OFFSET(filtri!$E$1;2;MATCH($G$5;filtri!$F$1:$I$1;0);COUNT.IF(OFFSET(filtri!$E$1;2;MATCH($G$5;filtri!$F$1:$I$1;0);20);"<>-")))

All the ranges are correct! But, when I try the execution of the code, the add method throws an error (number 13).

And, as I have already told before, if I insert the formula manually, it works correctly.

Does someone have any idea how to solve this problem?

Solution

After 3 days of tests, finally I find the solution. The error depends on some factors which I am about to explain.

First of all, the formula I wrote in the str variable is wrong because I used semicolons instead of commas. Then, I understood that the English is prefereable than Italian regarging the code.
The formula in the add method must be written in the standard notation, and not in the R1C1 one.
The validation list must be entered with the Excel sheet not protected by password (as I stupidly tried to do).

The corrected formula to use is the following (the other parts of the code remain unchanged):

str = "=IF($G$5="""",$AY$1,OFFSET(filtri!$" & v1 & "$1,2,MATCH($G$5,filtri!$" & v2 & "$1:$" & v4 & _
        "$1,0),COUNTIF(OFFSET(filtri!$" & v1 & "$1,2,MATCH($G$5,filtri!$" & v2 & "$1:$" & v4 & "$1,0),20),""<>-"")))"

Furthermore, there was a little mistake in the "filtri" sheet, but it had only a collateral influence on the above mentioned error.

I hope this could be useful for someone to avoid to loose the time I wasted.