help with automation macro ..

devonnicious · #1 (**permalink**) 08-11-04, 23:15

i know nuts about macros and i need some help in here.

ive to paste the following data into the excel file.

Return-Path:
<owner-extdom.svo-jpn@sj-core-3.cisco.com>
Received:
from gateway1a.dhl.com ([199.41.248.137]) by saturn.sin-ro.dhl.com (Netscape Messaging Server 3.6) with ESMTP id
AAA41CC for <gcs.sg.jpnlsc@dhl.com>; Wed, 11 Aug 2004 17:33:30 +0800

and ive to retrieve the DATE out from it, which is the "11 Aug 2004 17:33:30". i know i need to use the split function and split the sentence up and put them into an array or something like that. and after that i have to use the join function to join the subarrays which contains the dates.

could anyone give me a headstart or soemthing? cause ive really got no idea on how to go about doing it ...

many many thanks in advance ..

DavidCoutts · #2 (**permalink**) 08-12-04, 09:26

here's a start for you,
All ive done is split up the strings and store them to an array and if it's a date store it in a seperate array and then ouputs the dates to column B

Code:

Sub test()
Dim MyText As String
Dim MyTextArray As Variant
Dim OutputArray(10000) As Variant
Dim I As Integer, J As Integer

    J = 0
    For K = 1 To Range("A" & Rows.Count).End(xlUp).Row
    
    
        MyText = Range("A" & K).Formula
        MyTextArray = Split(MyText)
        For I = LBound(MyTextArray) To UBound(MyTextArray)
            On Error Resume Next
            OutputArray(J) = CDate(MyTextArray(I))
            If Err.Number = 0 Then
                J = J + 1
            End If
        Next I
        
        On Error GoTo 0
        For I = 0 To J
            If Not OutputArray(I) = "" Then
                Range("B" & I + 1).Formula = OutputArray(I)
            End If
        Next I
    Next K
        
End Sub

hope this helps a little

Dave

devonnicious · #3 (**permalink**) 08-13-04, 05:16

yeah .. thanks dude. everything's working now. thanks alot !

Internet Services

Web Development

Digital Marketing

Consumer Tech