Excel Macro Help: find, copy, transpose

[Closed]
Report
-
Posts
1
Registration date
Wednesday September 3, 2008
Status
Member
Last seen
September 7, 2008
-
Hello,
I need a macro that will go down column A and recognize the words "Defendant Address:" and then copy then transpose the info after it and the 2 lines below it to columns B, C, and D. In the example below, I need it to copy the text "Some guy" and place them in column B, the text "Some Address" in columb C (obviously in the row right next to the "Some guy" text) and the text "Some City, ST, Zip) and place it in the column D in the row next to the previous text. I need the macro to go all the way down the column and do this will all entries that have "Defendant Address". However, the tricky part is that sometimes the address will be blank! I need it to SKIP over that info if the address is blank (the line below that states Some address") would be a blank line. I would really appreciate any help with this. Thank you in advance!

Defendant: some guy




Defendant Address: some guy

Some address

Some City, ST, ZIP



Plaintiff: Someone else



Number: 12345


Vendor Number: 6789


Date: 8/28/2008


Filing Type: CIVIL SUIT


Case Type: CIVIL NEW FILING


Amount: $ 15,000


County Filed: HUDSON


Place Filed: NEW JERSEY SUPERIOR COURT, LAW DIVISION

1 reply

Dim cCell As Object 'Okay so what you need to do is create an object to work with the current cell so
Cells (1, "A").Select 'Then make sure you are starting at the very top so
For Each cCell In Range (Cells(1, "A"), Cells(1, "A").End(xlDown)) 'Then go through each cell in column A
If InStr(1, cCell.Value, "Defendant Address: ") > 0 Then ' Search for Defendant Address:
cCell.Offset(0, 1).Value = Mid(cCell.Value,19 , Len(cCell.Value)) 'copy name to next column cell
cCell.Offset(0, 2).Value = cCell.Offset(1,0).Value 'May need to increase as per how many rows gap between

. . . . .
End If
Next cCell

This should set you on your way, just need to add another IF block to catch the blank addresses fairly straight foward.
6
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1
Registration date
Wednesday September 3, 2008
Status
Member
Last seen
September 7, 2008

Thank you so much. I will definitely try this soon!!!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!