locked
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?

    Thanks.

    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".


    Cimjet

    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
    www.project-systems.co.nz

    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"
    etc.

    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?

    Thanks.

    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