formula from VBA not working

[Nils_Junker]

Hi everybody,

my VBA looks like this:
i.e.: table1Sheet.range("D221").Formula2 = "=INDIREKT(ADRESSE(193,VERGLEICH(Dashboard!$P$17,Tabelle1!D2:BD2,0)+3))"
now this brings up the error #NAME
(Formula2 because of the @ which would be added without it)

But if I put the formula in the sheet manually it works.
What is the problem here?
Thanks for your help!

 

[Alex Blakenburg]

What happens if you change Formula2 to Formula2Local ?

 

[Nils_Junker]

What happens if you change Formula2 to Formula2Local ?

then I get Runtimeerror 1004

 

[Alex Blakenburg]

Can you try putting your formula in a cell and then in the immediate window, print out the formula.
? Activecell.formula2
I am expecting it to give you the US / English version of what you are using

 

[Alex Blakenburg]

I have changed the language on my computer and if this worked in the spreadsheet:

=INDIREKT(ADRESSE(193,VERGLEICH(Dashboard!$P$17,Tabelle1!D2:BD2,0)+3))

Then this should work in the code:

table1Sheet.range("D221").Formula2Local = "=INDIREKT(ADRESSE(193,VERGLEICH(Dashboard!$P$17,Tabelle1!D2:BD2,0)+3))"

I am only repeating it here if you want to just try it again using copy/paste in case there were any typing errors.

 

[Nils_Junker]

I have changed the language on my computer and if this worked in the spreadsheet:

=INDIREKT(ADRESSE(193,VERGLEICH(Dashboard!$P$17,Tabelle1!D2:BD2,0)+3))

Then this should work in the code:

table1Sheet.range("D221").Formula2Local = "=INDIREKT(ADRESSE(193,VERGLEICH(Dashboard!$P$17,Tabelle1!D2:BD2,0)+3))"

I am only repeating it here if you want to just try it again using copy/paste in case there were any typing errors.

I really dont know what the problem is here.
I checked the typing several times but still get the error 1004.
Do you have any other Idea what the problem here could be.
Appreciate your help!

 

[Alex Blakenburg]

Can you copy in the message box you get with the 1004 error and also confirm that when you hit debug it goes to that exact line in the code ?
(the error doesn't say the sheet is protected does it ?)
Did you try my post#4 to see what the US version of the formula is ?

 

[Fluff]

Do you normally use commas as the separator in a formula or semi-colons?
Maybe it should be

table1Sheet.range("D221").Formula2Local = "=INDIREKT(ADRESSE(193;VERGLEICH(Dashboard!$P$17;Tabelle1!D2:BD2;0)+3))"

 

[Alex Blakenburg]

I am signing off for the night. Fluff may have more of a time zone overlap with you.

As near as I can figure it the US equivalent would be:
(not sure if it also defaults to your default delimiter or not - @Fluff will probably know)

table1Sheet.range("D221").Formula2 = "=INDIRECT(ADDRESS(193,MATCH(Dashboard!$P$17,D2:BD2,0)+3))"

Although something like this would seem to give you the same result:

table1Sheet.range("D221").Formula2 = "=INDEX($A$1:$BD$219,193,MATCH(Dashboard!P17,A2:BD2,0))"

 

[Nils_Junker]

Do you normally use commas as the separator in a formula or semi-colons?
Maybe it should be

table1Sheet.range("D221").Formula2Local = "=INDIREKT(ADRESSE(193;VERGLEICH(Dashboard!$P$17;Tabelle1!D2:BD2;0)+3))"

in the excel sheet i have to use semi-colons but in the code i have to use commas