0

I have a huge raw data from which I extract the date and other information using 2 VBA codes. However I am receiving "type Mismatch Error 13" in date column which i am not able to resolve, i have seen some articles but could not identify how to resolve them.

This below code is to activate the extract date function as i am using Excel 2016.

Public Function extractDate(rg As Range) As Date

Dim strDate As String:

strDate = extractText(" (\d{1,2} .{3} \d{4}) ", rg.text)

extractDate = strDate

End Function


Private Function extractText(pattern As String, text As String) As String
 
Dim regEx As RegExp: Set regEx = New RegExp

 Dim regEx_MatchCollection As MatchCollection

 Dim regEx_Match As Match
  
 regEx.pattern = pattern

 Set regEx_MatchCollection = regEx.Execute(text)

 If regEx_MatchCollection.Count = 0 Then
      extractText = vbNullString
 Else

    Set regEx_Match = regEx_MatchCollection(0)

    extractText = regEx_Match.SubMatches(0)
 End If

End Function

The below mentioned code is to extract date and other information from row data.

Sub TestThis()
    Dim LastRow As Long, ws As Worksheet
    Set ws = ActiveSheet

    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("D2:D" & LastRow).FormulaR1C1 = "=extractDate(RC[-1])"
    ws.Range("F2:F" & LastRow).FormulaR1C1 = "=IFERROR(LOOKUP(2^15,SEARCH({""Feed"",""Feed 1"",""Feed 2""},RC[-3]),{""Feed"",""Feed 1"",""Feed 2""}),""Combine"")"
    ws.Range("E2:E" & LastRow).FormulaR1C1 = _
    "=IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""EMIS - Burkina "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""Burkina "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+2),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9," & _
    "0},RC[-2]&""1234567890""))-1))=""EMIS - Naija "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""Naija "",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""A"",LEFT(RC[-2]," & _
    "MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+6),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))="""",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+8),IF((LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))-1))=""Afrique"",LEFT(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2]&""1234567890""))+6),LEF" & _
    "T(RC[-2],MIN(FIND({1,2,3,4,5,6,7,8,9,0},RC[-2] & ""1234567890""))-1))))))))"
End Sub

When i debut it highlights the line "extractDate = strDate" From first code.

James Z
  • 12,209
  • 10
  • 24
  • 44
Salman Shafi
  • 249
  • 9
  • Your function returns a date but you’re trying to assign a string as the return value – Tim Williams Jan 29 '23 at 07:13
  • 1
    **A tip:** Please avoid using `rg.text`. You may want to see [What is the difference between .text, .value, and .value2?](https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) – Siddharth Rout Jan 29 '23 at 07:33
  • 1
    Dear @SiddharthRout Great! this has resolved the issue. I have changed rg.text to "rg.value" – Salman Shafi Jan 29 '23 at 07:57

0 Answers0