|
Need help with a transpose
It looks like the layout is very nice:
Option Explicit Sub testme() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim oRow As Long Dim myStep As Long Dim myStr As String Dim CommaPos As Long Set CurWks = Worksheets("Sheet1") Set NewWks = Worksheets.Add myStep = 8 With CurWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow Step myStep myStr = Mid(.Cells(iRow, "A").Value, 15) CommaPos = InStr(1, myStr & ",", ",", vbTextCompare) myStr = Left(myStr, CommaPos - 1) NewWks.Cells(oRow, "A").Value = myStr NewWks.Cells(oRow, "B").Value _ = Trim(Mid(.Cells(iRow + 2, "A").Value, 28, 19)) oRow = oRow + 1 Next iRow End With End Sub Paul wrote: Hi everyone, I not too good a VB and still learing, but I need help with the following; I have this output. 7 lines followed by a blank in a text file. These repeat for about 3000 times all with different cn= name and last changed date. the 2nd line and subsequent lines are indented. I want to return only the cn= name and the date into two columns cn in col A and date in col B and remove all the other text. I can remove the text if someone can assist me with the code. Object DN: cn=yeotest,ou=people,o=awb EMail: Last Changed Date: 2008-08-24 21:51:42 Z Password Status: Enabled, Set Distribution Password Status: Set Simple Password Status: Set Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security Object DN: cn=yeoadmin,ou=people,o=awb EMail: [NONE] Last Changed Date: 2008-08-25 03:15:54 Z Password Status: Enabled, Set Distribution Password Status: Set Simple Password Status: Set Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security Object DN: cn=commsapprover,ou=people,o=awb EMail: [NONE] Last Changed Date: [UNKNOWN] Password Status: Enabled, Not set Distribution Password Status: Not set Simple Password Status: Not set Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security this is pretty much the output I want to see. Col A Col B yeotest 2008-08-24 21:51:42 yeoadmin 2008-08-25 03:15:54 commsapprover [UNKNOWN] Would appreciate any help on this. Cheers Paul -- Dave Peterson |
Need help with a transpose
Fantastic!
Works beautifully. Thanks very much Dave, really appreciate this. On Sep 1, 9:58*pm, Dave Peterson wrote: It looks like the layout is very nice: Option Explicit Sub testme() * * Dim CurWks As Worksheet * * Dim NewWks As Worksheet * * Dim iRow As Long * * Dim FirstRow As Long * * Dim LastRow As Long * * Dim oRow As Long * * Dim myStep As Long * * Dim myStr As String * * Dim CommaPos As Long * * Set CurWks = Worksheets("Sheet1") * * Set NewWks = Worksheets.Add * * myStep = 8 * * With CurWks * * * * FirstRow = 1 * * * * LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row * * * * oRow = 1 * * * * For iRow = FirstRow To LastRow Step myStep * * * * * * myStr = Mid(.Cells(iRow, "A").Value, 15) * * * * * * CommaPos = InStr(1, myStr & ",", ",", vbTextCompare) * * * * * * myStr = Left(myStr, CommaPos - 1) * * * * * * NewWks.Cells(oRow, "A").Value = myStr * * * * * * NewWks.Cells(oRow, "B").Value _ * * * * * * * * = Trim(Mid(.Cells(iRow + 2, "A").Value, 28, 19)) * * * * * * oRow = oRow + 1 * * * * Next iRow * * End With End Sub Paul wrote: Hi everyone, I not too good a VB and still learing, but I need help with the following; I have this output. 7 lines followed by a blank in a text file. These repeat for about 3000 times all with different cn= name and last changed date. the 2nd line and subsequent lines are indented. I want to return only the cn= name and the date into two columns cn in col A and date in col B and remove all the other text. *I can remove the text if someone can assist me with the code. Object DN: cn=yeotest,ou=people,o=awb * * * * EMail: * * * * Last Changed Date: 2008-08-24 21:51:42 Z * * * * Password Status: Enabled, Set * * * * Distribution Password Status: Set * * * * Simple Password Status: Set * * * * Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security Object DN: cn=yeoadmin,ou=people,o=awb * * * * EMail: [NONE] * * * * Last Changed Date: 2008-08-25 03:15:54 Z * * * * Password Status: Enabled, Set * * * * Distribution Password Status: Set * * * * Simple Password Status: Set * * * * Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security Object DN: cn=commsapprover,ou=people,o=awb * * * * EMail: [NONE] * * * * Last Changed Date: [UNKNOWN] * * * * Password Status: Enabled, Not set * * * * Distribution Password Status: Not set * * * * Simple Password Status: Not set * * * * Password Policy DN: cn=Universal Passwords,cn=Password Policies,cn=Security this is pretty much the output I want to see. Col A * * * * * Col B yeotest * * * * 2008-08-24 21:51:42 yeoadmin * * * *2008-08-25 03:15:54 commsapprover * [UNKNOWN] Would appreciate any help on this. Cheers Paul -- Dave Peterson- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com