Q about settings and commands in Excel 2013

[Robin121]

Hi guys,

--using Excel 2013
--VBA 7.1
--dutch language

By clicking a button, I would like to paste a formula into a cell that either fills the cell based on the result of the formula, or leaves it empty.
For that, I use:

Sheets("DSS voorafgaand aan competitie").Select
Worksheets("DSS voorafgaand aan competitie").Range(BS7).Formula = "=ALS(BP7="";"";VERT.ZOEKEN(BP7;Kladblok!BP7:DO16;4))"
--(more rows)--


which results in the error:

"Error 1004 during execution"
"Application or object defined error"

I then tried the work-around with a loop:

Dim k as Integer
k = 7
Do Until k = 17
Worksheets("DSS voorafgaand aan competitie").Cells(k, 73).FormulaR1C1 = "=IF(RC[-5]="""","""",VLOOKUP(RC[-5],Kladblok!R7C68:R16C119,6))"
--other rows--
k = k + 1
Loop

That works fine. But why does VBA only understand the R1C1 structure and commands in english? My Excel is set to dutch..?

 

[Fluff]

In the first attempt you need to enclose the range in quotes "BS7", you also need to double up on the quotes in the formula.
Not sure if you need to replace the ; with ,

 

[Marcelo Branco]

Maybe this

Worksheets("DSS voorafgaand aan competitie").Range(BS7).[COLOR=#ff0000]FormulaLocal[/COLOR] = "=ALS(BP7=[COLOR=#ff0000]""""[/COLOR];[COLOR=#ff0000]""""[/COLOR];VERT.ZOEKEN(BP7;Kladblok!BP7:DO16;4))"

M.