Excel Macro Help: find, copy, transpose

Jay, on Thursday September 4, 2008 10:44:36 PM

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

Configuration: Windows XP
Internet Explorer 6.0

Reply to Jay Report this message to moderators Go to last message

This message seems useful, vote!
Report this message to moderators

Jimmy, on Saturday September 6, 2008 03:28:59 PM

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.

Reply to Jimmy

This message seems useful, vote!
Report this message to moderators

bhattjai, on Monday September 8, 2008 01:57:42 AM

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

Reply to bhattjai

Ask your question Reply

Results for

A pretty basic excel macro question Hello, I need some helping creating a macro that will copy a column of data from one sheet and transpose it onto a row in another sheet. Thats the easy part. The thing that I cant figure out how to do is to make a macro so that every time I run it, it... en.kioskea.net/forum/affich-23113-a-pretty-basic-excel-macro-question

Excel Macro Help Please (Solved) Hello Everyone, I am sking for some help with an Excel macro please. I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with... en.kioskea.net/forum/affich-11146-excel-macro-help-please

Excel Macro for deleting rows IF cells not... Hello, I need help to create a Excel macro. Basicly what I have is a sheet full with information about different departments and what I want to do is delete every row EXCEPT the rows that contain some specified values (wich i would like to enter on... en.kioskea.net/forum/affich-12620-excel-macro-for-deleting-rows-if-cells-not

1 2 3 Next

Results for

Copy/Paste problems when using FirefoxCopy/Paste problems when using Firefox Adware Pushow Extensions It happens that sometimes, Firefox refuse to copy/paste items. Two assumptions can be made: Adware Pushow You have been infected by an adware, named pushow**.dll... en.kioskea.net/faq/sujet-496-copy-paste-problems-when-using-firefox

To insert an image in the FAQTo insert an image in the FAQ Method Where to find images? Inserting screenshots can help to understand an explanation. Here is how to improve tips on Kioskea (FAQ). For this trick, we opted to use the site Imageshack.us. Note:... en.kioskea.net/faq/sujet-67-to-insert-an-image-in-the-faq

1 2 3 Next

Results for

Excel Macro Help pleaseI need help with an excel macro that will take a username (string) in the first column and look at everytime that they either have an P (present), T (tardy), E (Excused), or A (absent) in a second column. They will be given 400 points if they were... en.kioskea.net/forum/affich-33502-excel-macro-help-please

Excel MacroI need help with an excel macro that will take a username (string) in the first column and look at everytime that they either have an P (present), T (tardy), E (Excused), or A (absent) in a second column. They will be given 400 points if they were... en.kioskea.net/forum/affich-33540-excel-macro

Excel Macro Help neededI need help with an excel macro that will take a username (string) in the first column and look at everytime that they either have an P (present), T (tardy), E (Excused), or A (absent) in a second column. They will be given 400 points if they were... en.kioskea.net/forum/affich-33099-excel-macro-help-needed

1 2 3 Next

Results for

Spreadsheets - The Excel Interface Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel document is called a file... en.kioskea.net/tableur/tabinterface.php3

Databases - ODBC (Open Database Connectivity) ODBC stands for Open Database Connectivity. This is a Microsoft-defined format for communicating between Windows database clients and consumer DBMSs. The ODBC managier can be found in Windows 3.1 and 9x. In Windows 95 and 98, it is available in the... en.kioskea.net/bdd/odbc.php3

1 2 3 Next