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 City, ST, ZIP
Plaintiff: Someone else
Vendor Number: 6789
Filing Type: CIVIL SUIT
Case Type: CIVIL NEW FILING
Amount: $ 15,000
County Filed: HUDSON
Place Filed: NEW JERSEY SUPERIOR COURT, LAW DIVISION
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
. . . . .
This should set you on your way, just need to add another IF block to catch the blank addresses fairly straight foward.