Formula in a macro doesnt work for Spanish Excel user

Not sure if this is a formula question, a Macro question or an Excel question.
I have a macro that I wrote that will not work when I share it with a co-worker who's excel is in Spanish. The line of code takes the date that is written "W43-2018\22th October" and translates it to "10/22/2018". The Macro code is :
ActiveCell.FormulaR1C1 = "=DATE((MID(R[-1]C,5,4)),(MONTH((DATEVALUE(MID(R[-1]C,FIND(""th"",R[-1]C,1)+3,LEN(R[-1]C)-(FIND(""th"",R[-1]C,1)+3-1)))))),(MID(R[-1]C,10,(FIND(""th"",R[-1]C,1)-1-10)+1)))"

which enters this formula:
"=DATE((MID(D1,5,4)),(MONTH((DATEVALUE(MID(D1,FIND("th",D1,1)+3,LEN(D1)-(FIND("th",D1,1)+3-1)))))),(MID(D1,10,(FIND("th",D1,1)-1-10)+1)))"

but when the middle part "MONTH((DATEVALUE(MID(D1,FIND("th",D1,1)+3,LEN(D1)-(FIND("th",D1,1)+3-1))&1)))" returns October, the Spanish user gets an error. This works fine for all English users. We tried switching her language to English to run it and we got the same issues. any idea how to fix this? We cannot change the way the date comes in (W43-2018\22th October) either.

the spanish formel for that is more or less like that :

=FECHA((LARGO(D1,5,4)),(MES((FECHANUMERO(EXTRAE(D1,BUSCAR("th",D1,1)+3,LARGO(D1)-(BUSCAR("th",D1,1)+3-1)))))),(EXTRAE(D1,10,(BUSCAR("th",D1,1)-1-10)+1)))

TRUE ...

Originally Posted by protonLeah

Is it necessary for you to put a formula vs the date itself?

As in post #2. After seven responses we are still waiting for the return of the OP.

The date comes from the customer and can't be changed. My macro then takes the date and re-formats it to a usable format. I have no issues with moving the reformat to vb, I just don't know how.

Sorry if the code above was inaccurate, here it is:

ActiveCell.FormulaR1C1 = "=DATE((MID(R[-1]C,5,4)),(MONTH((DATEVALUE(MID(R[-1]C,FIND(""th"",R[-1]C,1)+3,LEN(R[-1]C)-(FIND(""th"",R[-1]C,1)+3-1))&1)))),(MID(R[-1]C,10,(FIND(""th"",R[-1]C,1)-1-10)+1)))"

Here's what the formula should come out as:
=DATE((MID(D1,5,4)),(MONTH((DATEVALUE(MID(D1,FIND("th",D1,1)+3,LEN(D1)-(FIND("th",D1,1)+3-1))&1)))),(MID(D1,10,(FIND("th",D1,1)-1-10)+1)))

10-29-2018 2-00-22 PM.jpg
This is a screen shot of how it comes from the customer. There is always a "th" following the day of the month.

10-26-2018 10-32-05 AM.jpg

This is what it looks like to her.

I tried making that change and it still resulted in an error. I went through the Macro with her step by step through the Debugger and the line "DV = DateValue(DayNum & " " & Mnth & " " & Yr)" is the line that's causing it to fail. She's getting a type mismatch error. Any idea?


11-1-2018 3-48-55 PM.jpg

ProtonLeah: I'm not sure what screenshot you are referring to, the only difference between the macro you attached and the code in the screen shot directly above is the "sht." before the Cells. I added that after the suggestion above.

Keebellah: This is what showed up in the Intermediate window

11-2-2018 11-20-34 AM.jpg

The intermediate window, no errors when showing the results?

I got the same error, a type Mismatch and this is what printed to the Intermediate window.

Protonleah: Here's the full code,

Please Login or Register  to view this content.

This is what the customer incoming file looks like:

11-2-2018 3-17-30 PM.jpg

and this is what the output should look like:
11-2-2018 3-20-26 PM.jpg

Keebellah: DV = DateValue(DayNum & " " & Mnth & " " & Yr) is the line of code that is making the macro error.

=DATEVALUE(MID(D1,FIND("\",D1)+1,FIND(" ",D1)-FIND("\",D1)-3) &" " & MID(D1,FIND(" ",D1)+1,15)&MID(D1,FIND("\",D1)-4,4))
Will always return VALUE error in any system other than English.

The problem is the system locale, it's in Spanish, and you're parsing a month string "October", which has no meaning and can't be translated into a proper month number, as the string for Month 10 is "Octubre" in Spanish.

SYSTEMLOCALE = Application.LanguageSettings.LanguageID(msoLanguageIDUI)
Spanish locale is 1033, so you could do a:
IF SYSTEMLOCALE = 1033
CHANGE MONTH STRINGS FROM ENGLISH TO SPANISH: Enero, Febrero, Marzo... an so on

Hope that helps a little.

As I said, translate to locale, or, you can create an array with your strings, and pull the month number directly from it...
as protonLeah did:

Originally Posted by protonLeah

so:

Please Login or Register  to view this content.

This is a very nice aproach:

Mnth = WorksheetFunction.Match(Temp(1), MoNames, 0) * 1

Sorry for the delay, I had to wait until I could test with the user. This worked perfectly! Thank you so much for all your help!