VBA: Searching and extracting

[Viewson]

Hi All,

From the original sentence below, I am trying to parsing out a certain section in regards to paydate. Then using the second code, I will parse only the paydate. In excel using formula this works, but when trying to work this in a macro, I am getting an error.

Original Sentence: MANDATORY. RIO CA#509513 EX DATE: 3/16/11,PAYDATE:3/23/11.PLEASE CONTACT QC GROUPS LABELS

Code 1: Extract Partial data for Paydate

 [/FONT]
[FONT=Arial]ActiveCell.FormulaR1C1 = "=IF(AND(ISERR(SEARCH("Payd",G2)),ISERR(SEARCH("Pay d",G2))),"",MID(G2,SEARCH("Pay",G2),20))"[/FONT]
[FONT=Arial]

Output in excel: PAYDATE:3/23/11.PLEA

Code 2: Extract only Paydate

 [/FONT]
[FONT=Arial]=MID(L4,MIN(FIND(0,SUBSTITUTE(L4,{1,2,3,4,5,6,7,8,9},0)&0)),LEN(L4)-MIN(FIND(0,SUBSTITUTE(L4,{1,2,3,4,5,6,7,8,9},0)&0))-MATCH(1,INDEX(--ISNUMBER(--MID(L4,LEN(L4)+1-
ROW(INDIRECT("1:"&LEN(L4))),1)),0),0)+2[/FONT]
[FONT=Arial]

Final output (Goal): 3/23/11

 

[alan.m]

Hi Viewson,
try

Sub findPaydata()
    startTxt = InStr(1, Cells(2, 7), "PAYDATE") + 8
    endTxt = InStr(startTxt, Cells(2, 7), ".")
    lengthDate = endTxt - startTxt
    myText = Mid(Cells(2, 7), startTxt, lengthDate)
End Sub