Dynamic named range error - cannot add formula

Hi,

This post is just to help if you experience the same problem I have experienced.

First of all, I am from South Africa and I'll explain why this actually caused the error.

I am new to VBA and coding and find the tutorials on the internet very helpfull in achieving my goal but I was ready to give up after experiencing an error which I couldn't figure out for 2 days. When creating a dynamic named range you will use a formula simmilar to "=OFFSET(Data!$N$9,0,0,COUNTA(Data!$N$9:$N$9989),7)". I would keep getting an error when trying to save this in Name Manager and could not see anything wrong with the formula as I copied it directly from a tutorial which seemed to work just fine.

Long story short, the error was caused by the seperators (commas) used in the formula. Being from South Africa, I have set up my Region to "English (South Africa)" and for some reason this causes the seperators not to be regognized by excel. The moment I changed to "English (United States)", the error message dissapeared and the dynamic named range functioned as intended.

Hope this helps someone to figure out what's wrong if you experience the same.

Regards

Douglas

This helped me with a problem had struggled with for days.

Turns out, even if you are using ; (semi-colon) as the separator for formulas in Excel VBA will want , (comma) as separator when entering formulas to be used in Excel. I was creating named ranges with the help of VBA, using OFFSET and thought I should use ; as this is what I use in Excel. If anyone else has the same problem.