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

Question

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?

The debug.print of the string str gives the following result: ... - which is rather weird because this result is not at all what you are putting in str in your code. — GSerg
– GSerg, Commented Sep 1 at 11:32
Shouldn't that be =SE not =IF at the start of your formula? — Rory
– Rory, Commented Sep 1 at 12:28
Sorry, I insered an uncorrect string. I wrote the code above in a hurry. Now it is the right one. Anyway, it is still not working. — BennyB
– BennyB, Commented Sep 1 at 15:24
Have you tried putting the localized version of the formula in str, with SE etc? — GSerg
– GSerg, Commented Sep 1 at 15:44

BennyB · Accepted Answer · 2025-09-02 12:33:42Z

2

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.

edited Sep 2 at 12:33

answered Sep 2 at 12:33

BennyB

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Williams Sep 3 at 16:18

I think you can accept your own answer - it helps other users to know the post has a solution.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related