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

 

[Norie]

What string are you looking for and what do you want to happen when it's found?

 

[zany username]

If the cell contains "Payment_made: Yes" then it will

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

and carry on to

oCell.Offset(0, 5).Value = Split(Split(oCell.Value, "Payment_method:")(1), "Payment_date:")(0)

 

[Norie]

To check if oCell contains 'Payment_Made:' you can use this.

Pos = InStr(oCell.Value, "Payment_Made:")

If Pos > 0 Then
    ' oCell contains 'Payment_Made:'
End If

Note Pos will be the index of the character where the string you are looking for starts, for example if the string was 'Test Payment_Made: Yes' then Pos would be 6.

 

[zany username]

Thanks for you help so far Norie.

What's happening is currently when I try to extract the Payment_Method: I get Run-time error '9': Subscript out of range.
I take it that is because the cell it is looking at doesn't contain Payment_method: because they answered No to the above question.

How do I make an If the cell contains "Payment_made:Yes" Then extract their Payment_method and have it carry on to extract Payment_date?

If oCell.Value, "Payment_Made:" Then
oCell.Offset(0, 5).Value = Split(Split(oCell.Value, "Payment_method:")(1), "Payment_date:")(0)

End If

Next oCell

?

 

[Norie]

You can use InStr, as I did in the code I posted, to see if a substring is contained within a string.

An alternative would be to not try to do everything in one line.

 

[zany username]

I have no experience with any coding, I've only managed to get this far out of sheer desperation and countless Google searches.

I've tried putting what you provided me into the existing code I found but keep getting compile errors or it doesn't do anything. Would you mind putting this together, please?
</SPAN>

 

[Norie]

Based on the few lines of code you've posted.

Pos = InStr(oCell.Value, "Payment_Made:")

If Pos > 0 Then
   oCell.Offset(0, 5).Value = Split(Split(oCell.Value, "Payment_method:")(1), "Payment_date:")(0)
End If

By the way, you could avoid errors by not doing everything on one line.

 

[zany username]

That's how I had tried it. I don't receive any errors but it's not doing anything - it's not extracting anything from the target or updating the spread-sheet.

Public Sub ExtractPayment_date()

Dim oTarget As Range
Dim oCell As Range

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

For Each oCell In oTarget
    
Pos = InStr(oCell.Value, "Payment_Made:Yes")
If Pos > 0 Then
oCell.Offset(0, 6).Value = Split(Split(oCell.Value, "Payment_method:")(1), "Payment_date:")(0)   
End If

Next oCell
End Sub

 

[Norie]

Is it definitely 'Payment:Yes' without a space?