Excel Macro Help: find, copy, transpose Closed

Question

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

Jimmy · Accepted Answer

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.

Excel Macro Help: find, copy, transpose

1 response

Similar discussions

Subscribe To Our Newsletter!