Asked by:
VBA formula
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:
Applicationdefined or objectdefined 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.projectsystems.co.nzFriday, 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:
Applicationdefined or objectdefined 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 USCentric, 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 reenter the working formula in the cell to get the correct code.
 Edited by Bernie Deitrick, Excel MVP 20002010 Friday, March 20, 2015 10:15 PM
Friday, March 20, 2015 10:14 PM 
I replaced the semicolon 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