VBA formula RRS feed

  • Question

  • Hello All,

    I need use formula in VBA:

    cntFormula = "=INDEX(Translations!$C$3:$D$53;MATCH(""txtCounty"";Translations!$B$3:$B$53;0);Language)" & cntRow + 1

    but system displays error:

    Application-defined or object-defined error

    What is wrong? Could you help me?


    Friday, March 13, 2015 3:51 PM

All replies

  • Hi

    I notice that you're using "semicolon" to separate the parameter in the formula and that's okay in some part of Europe but in America we use "comma".


    Friday, March 13, 2015 6:00 PM
  • If you are assigning that as a formula in a cell, then you need a valid formula.  Maybe ???

    cntFormula = "=INDEX(Translations!$C$3:$D$53;MATCH(""txtCounty"";Translations!$B$3:$B$53;0);Language) " & cntRow + 1

    or ,aube

    cntFormula = "=INDEX(Translations!$C$3:$D$53;MATCH(""txtCounty"";Translations!$B$3:$B$53;0);Language) - " & cntRow + 1

    Friday, March 13, 2015 6:05 PM
  • Assuming that you are looking for txtCounty and it is not a text box, that Language is a named range on a sheet, then try this

        cntFormula = ActiveSheet.Evaluate("INDEX(Translations!$C$3:$D$53,MATCH(""txtCounty"",Translations!$B$3:$B$53,0),Language)") & cntRow + 1

    Thursday, March 19, 2015 11:54 PM
  • Try:

    Application.WorksheetFunction.Index(Translations!$C$3:$D$53;MATCH(""txtCounty"";Translations!$B$3:$B$53;0);Language)" & cntRow + 1

    But as the others said you may well need to use a comma to separate the parameters for VBA.

    Rod Gill
    Author of the one and only Project VBA Book

    Friday, March 20, 2015 4:52 AM
  • Thanks for all your replies.
    My table has rows and each row is headed.
    When I click the button to add a new row I also add a row heading in the table.
    If I change the language using radiobutton (without macro) so I want to display the correct name of the row in the table from the dictionary.

    Therefore, I need to insert a formula when creating a new row in the table using a macro.

    I use this formula and formula working correct in excel:

    =INDEX(Translations!$C$3:$D$54;MATCH("txtCounty";Translations!$B$3:$B$54;0);Language) & "1"
    =INDEX(Translations!$C$3:$D$54;MATCH("txtCounty";Translations!$B$3:$B$54;0);Language) & "2"

    But if I insert formula using macro

    [DefCounties].End(xlDown).Offset(1, 0).Formula ="=INDEX(Translations!$C$3:$D$53;MATCH(""txtCounty"";Translations!$B$3:$B$53;0);Language)" & cntRow + 1

    I get error:
    Application-defined or object-defined error

    I dont know why. What is your advice?

    Thanks in advance.

    Friday, March 20, 2015 7:51 PM
  • First, make sure you have a cell, and the correct cell:

    Msgbox [DefCounties].End(xlDown).Offset(1, 0).Address

    Friday, March 20, 2015 8:18 PM
  • I am sure I have correct cell.
    Friday, March 20, 2015 8:40 PM
  • Try your formula with , in place of ; -  VBA is US-Centric, and that may be your issue. Also, your range is 3:53 in your code, and 3:54 in your formulas (though that should not matter).

    [DefCounties].End(xlDown).Offset(1, 0).Formula ="=INDEX(Translations!$C$3:$D$53,MATCH(""txtCounty"",Translations!$B$3:$B$53,0);,anguage)" & cntRow + 1

    Whenever I do formulas, I get the formula to work and record a macro where I re-enter the working formula in the cell to get  the correct code.

    Friday, March 20, 2015 10:14 PM
  • I replaced the semi-colon after the comma in my formula, but I keep getting error.

    Could you send your sample excel, pls? Is it possible?


    Friday, March 20, 2015 10:43 PM
  • I fix my problem now it works correct:

     [DefCounties].End(xlDown).Offset(1, 0).Formula = "=INDEX(Translations!$C$3:$D$53,MATCH(""txtCounty"",Translations!$B$3:$B$53,0),Language)" & "&" & cntRow + 1

    Friday, March 20, 2015 11:08 PM
  • Thanks everybody for your help.
    Friday, March 20, 2015 11:09 PM