none
PROBLEMS WITH ACTIVECELL.FORMULA RRS feed

  • Question

  • The objective is to get the selected text value in a combo box (not the number). The combo box is in worksheet "Test" and refers to a list in column A in worksheet "Languages" and a reference value in column B. The result is shown in "Test".

    dim strx as string

    strx = "=INDIRECT(CONCATENATE(" & """Languages!$A$""" & ";" & "INDIRECT(CONCATENATE(" & """Languages!$B$""" & ";ROW()))))"

    Debug.Print strx ' Prints as =INDIRECT(CONCATENATE("Languages!$A$";INDIRECT(CONCATENATE("Languages!$B$";ROW()))))

    Range("F2").Activate  

        ActiveCell.FormulaR1C1 = strx ' doesn't work - gives an error: Application-defined or Object-defined error.

    ' If =INDIRECT(CONCATENATE("Languages!$A$";INDIRECT(CONCATENATE("Languages!$B$";ROW())))) is copied into the Excel Worksheet, it works OK.  

    Tuesday, March 6, 2018 1:40 PM

All replies

  • VBA always uses USA settings. In particular, you must specify comma to separate the arguments in a formula:

        strx = "=INDIRECT(CONCATENATE(" & """Languages!$A$""" & "," & "INDIRECT(CONCATENATE(" & """Languages!$B$""" & ",ROW()))))"

    or a bit shorter

        strx = "=INDIRECT(CONCATENATE(""Languages!$A$"",INDIRECT(CONCATENATE(""Languages!$B$"",ROW()))))"

    Apart from that, you should use Formula instead of FormulaR1C1, since you create a formula in A1 notation, not in R1C1 notation.

    Finally,

        Range("F2").Activate
        ActiveCell.Formula = strx

    can be shortened to

        Range("F2").Formula = strx

    Not selecting a cell unless it is strictly necessary is more efficient.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 6, 2018 2:12 PM