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.

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.

Protonleah: Here's the full code,

Sub copy()
'
' copy Macro


On Error GoTo errorHandler

Dim lNumberOfRows As Long
Dim aNumberofRows As Long
Dim NumberofCol As Long

Dim strForecastFileName As String
Dim FilePath As Variant
Dim length As Integer
Dim CurrentMonth As Integer
Dim tdate As Date
Dim XrefLength As Integer
Dim XtractLength As Integer
Dim rng As Range

     Dim lastColumn As Integer
    Dim rng_source As Range
    Dim rng_Destination As Range
    Dim l_SourceRows As Long



Sheets("Forecast").Select
    Range("A1:CZ9000").Select
       Selection.ClearContents
       
       Sheets("Forecast2").Select
    Range("A1:CZ9000").Select
       Selection.ClearContents
       
         Sheets("Combine").Select
    Range("A1:CZ9000").Select
       Selection.ClearContents
        
Sheets("upload").Select
    Range("A2:CZ9000").Select
       Selection.ClearContents

FilePath = Application.GetOpenFilename(, , "Select first forecast data")

If FilePath = False Then
    MsgBox "No file was selected.  Please select a file."
 Exit Sub
End If

    'Opens the file
    Workbooks.Open FilePath, ReadOnly:=True
    Filename = Workbooks(Workbooks.Count).Name
    Workbooks(Filename).Activate
    Sheet = ActiveSheet.Name
    
     Sheets("Sheet1").Activate
    
    ' copy part numbers

 Set rng = Application.InputBox("Select Cross reference numbers including Heading", "Obtain Range Object", Type:=8)

    rng.copy
    
    Windows("Mexico_Forecast_Macro.xlsm").Activate
  Sheets("Forecast").Activate
  ActiveSheet.Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    lNumberOfRows = Range("A3").End(xlDown).Row
    
  Workbooks(Filename).Activate
    Sheet = ActiveSheet.Name
    
     Sheets("Sheet1").Activate
    
    Set rng = Application.InputBox("Select Forecast Data including dates", "Obtain Range Object", Type:=8)

    rng.copy
    
    Windows("Mexico_Forecast_Macro.xlsm").Activate
      Sheets("Forecast").Activate
    ActiveSheet.Range("D1").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
aNumberofRows = Range("A3").End(xlDown).Row
 
 
 Sheets("Forecast").Activate
 
 Range("D2").Select
  Range(Selection, Selection.End(xlToRight)).Select
    Selection.Insert Shift:=xlDown
     Range("D2").Select
     
     
     Call DateMod
     

 
     
Range("D3").Select
  Range(Selection, Selection.End(xlToRight)).Select
    Selection.Insert Shift:=xlDown
         
 Range("D3").Select
 ActiveCell.FormulaR1C1 = _
      "=R[-1]C"
        
      Set rng_source = Range("D3")
    l_SourceRows = rng_source.Rows.Count
    lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
    Set rng_Destination = Range(rng_source.Cells(1), Cells(rng_source.Cells(1).Row + l_SourceRows - 1, lastColumn))

    rng_source.AutoFill _
        Destination:=rng_Destination, _
        Type:=xlFillDefault
    
    Range("b3").Select
      ActiveCell.FormulaR1C1 = "Supplier Number"
      
    Range("B4").Select
      ActiveCell.FormulaR1C1 = "1098"
    Selection.AutoFill Destination:=Range("B4:B" & lNumberOfRows), Type:=xlFillDefault
    
     Range("C3").Select
      ActiveCell.FormulaR1C1 = "Short Number"
      
    Range("C4").Select
      ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2]&""*"",XRef!C[-2]:C[-1],2,FALSE)"
    Selection.AutoFill Destination:=Range("C4:c" & lNumberOfRows), Type:=xlFillDefault
    
    Range("D4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "General"
     
   Sheets("control").Activate
 
 Range("a2").Select
    
Exit Sub


       
  
 ''End If

errorHandler:
MsgBox Err.Description
MsgBox Err.Number



End Sub

Sub DateMod()
    Dim Temp    As Variant, _
        Prefix  As Variant, _
        Yr      As Variant, _
        Mnth    As String, _
        DayNum  As String, _
        DV      As Date
        
       Dim lastColumn As Long
       Dim i As Integer
       Dim sht As Worksheet
       
Set sht = ThisWorkbook.Worksheets("Forecast")
Range("D1").Select
 lastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column

   
    For i = 4 To lastColumn
    
    
    Temp = Split(Cells(1, i).Value, "\")
    Yr = Split(Temp(0), "-")
    Prefix = Yr(0)
    Yr = Yr(1)
    Temp = Split(Temp(1), " ")
    Mnth = Temp(1)
    DayNum = Left(Temp(0), Len(Temp(0)) - 2)
    
    DV = DateValue(DayNum & " " & Mnth & " " & Yr)
    
    Cells(2, i).Value = DV
    Next i
End Sub

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

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:

Sub DateMod()
    Dim Temp    As Variant, _
    Prefix  As Variant, _
    Yr      As Variant, _
    Mnth    As Long, _
    DayNum  As String, _
    DV      As Date, _
    MoNames As Variant
    
    Dim lastColumn As Long
    Dim i As Integer
    Dim sht As Worksheet
    
    MoNames = Array("January", "February", "March", "April", _
    "May", "June", "July", "August", _
    "September", "October", "November", "December")
    
    Set sht = ThisWorkbook.Worksheets("Forecast")
    Range("D1").Select
    lastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column
    
    For i = 4 To lastColumn
        Temp = Split(Cells(1, i).Value, "\")
        Yr = Split(Temp(0), "-")
        Prefix = Yr(0)
        Yr = Yr(1)
        Temp = Split(Temp(1), " ")
        Mnth = WorksheetFunction.Match(Temp(1), MoNames, 0) * 1
        DayNum = Left(Temp(0), Len(Temp(0)) - 2)
        DV = DateSerial(Yr, Mnth, DayNum)
        Cells(2, i).Value = DV
    Next i
End Sub

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!