How do I set conditions before using "split"

[zany username]

I'm completely new to VBA but I found some very helpful code in an older thread: http://www.mrexcel.com/forum/excel-questions/543349-extract-text-between-two-points.html

Using "Split":

Public Sub Test1()

Dim oTarget As Range
Dim oCell As Range

Set oTarget = ActiveSheet.Range("A2:A2043")

For Each oCell In oTarget

oCell.Offset(0, 4).Value = Split(Split(oCell.Value, "Date_stamp:")(1), "Payment_made:")(0)

Next oCell

End Sub

Thanks Gary, that's accomplised what I was originally failing to do with MID() but what I'm hoping someone can show me now is how I can set conditions on using Split.


I have dumped data from emails into Excel 2010 in cells A2:A2043 and I'm spliting it for ease of viewing but I need the code to check if the cell contains a specific string and if skip to the next cell if it doesn't. For instance when the customer hasn't made a payment it will skip as there isn't anything to split after that point and continue spliting if they answered Yes.
Examples are:

Customer_Number: 11338849 Invoice_Period: 30/06/13 Attendance_type: Group BookingDate_stamp: 03/07/2013 Payment_made: No

Customer_Number: 11338849 Invoice_Period: 30/06/13 Attendance_type: Group BookingDate_stamp: 03/07/2013 Payment_made: Yes Payment_method: Cheque Payment_date: 14/06/2013

<TBODY>
</TBODY>

Ideally I would have extracted directly from Outlook but that's a different problem for another day at this stage, so I wan't to carry on from where I am. Also, how am I going to extract that last date Can I Split between "Payment_date:" and the end of the cell?

Best regards,
Zany

 

[zany username]

Ah, no it's Payment_made: Yes. But I've corrected that and it's still not working. The sub-strings are:

Customer_Number: 11338849

Invoice_Period: 30/06/13

Attendance_type: Group

BookingDate_stamp: 03/07/2013

Payment_made: Yes

Payment_method: Cheque

Payment_date: 14/06/2013


I eventually had to alpha-sort the spreadsheet based on Yes/No in column 5 and start the script from halfway through where all the proceeding cells were Payment_made: Yes.
And I resorted to using FIND("_date:",A:A) to get the position of the payment date and refernced that result with =MID(A:A,J38,17) to extract _date: 14/06/2013. What would be a better way to get the value for Payment_date?