Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

**Groovo** · 05-30-2013, 10:20 PM

Hello all,

I have more than 50,000 lines of data which I have formatted to show errors by highlighting the row in yellow. Approximately 15% of the lines are yellow (have the error "#N/A"). I don't want to correct the #N/A problem. It is there because of the format of the input data and is the key that I use to identify the problem with the input. I can manually resolve this issue by copying the essential data from cell E(?) in the effected row into cell A(?+1) and delete row (?). This task is..... daunting at best. After correcting this data I will import the next 50,000 lines and perform the process again. I have done it manually twice already.

The question is, how do I write a macro to do this automatically? I have VERY little experience with macros. I have written (with help from internet searches) 2 small macros for other issues with this same data, so I know how to use the VB editor, barely. I believe that it will involve xxxxxx.value, and copy and paste functions, but I have no idea how to write the code.

It also may be important to know that the effected fields are text strings. If it is possible, I want to trim the data (no beginning or ending spaces) and change the remaining spaces between words to ">".

I am not able at this time to upload a sample, so I hope I described the situation well enough. Let me know if further clarification is needed.

Thank you.

**dilipandey** · 05-31-2013, 02:54 AM

Hi Groovo,

Welcome to the forum.

Please upload a sample workbook with dummy data to support your query. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Groovo** · 05-31-2013, 03:27 AM

In the attached file, column E is my input column. Column A is for corrections. All other columns are calculated. No calculated fields may be altered. On the yellow lines, I need to check for the error in column B, Copy the text in column E to the next line in column A, then delete the yellow line. I want to run this on the entire sheet one time to correct all errors.

Thanks for helping.

**AB33** · 05-31-2013, 04:54 AM

Please Login or Register  to view this content.

**Groovo** · 05-31-2013, 05:11 AM

Thanks AB33!

I tried it on the test file and it worked great except that the space between the 2 words on line 25 cell E needs to have the space made into the ">" character. There will be lines that have more than 1 space in the text, 1 between each word.

Does this macro trim the spaces from the ends of the text?

I also have a concern regarding the "*" used in the macro. Is that a wildcard? Will it behave badly if I have "*" in my text?

Could you explain how this macro works so that I can learn what the commands mean?

THANKS!!

**AB33** · 05-31-2013, 05:23 AM

I do not get your question. You do not need to include a trim on the code, you could have trimmed once the row is copied in to column A. I have removed your concern on wild card and also included a trim on column E. I have amended the code

**Groovo** · 05-31-2013, 05:30 AM

I see that you changed the "*" to "B". I don't know the function of that letter. Is it a variable? I want to learn what each line does.

Thank you for adding the trim. I need that because of the other step that is still missing. All of the remaining spaces in the copied string need to be changed to the character ">". There should be no spaces in the final string in the A column. There may be more than 1 space because there may be more than 2 words in the string.

Can you add comments to the macro to explain what is going on so that I can learn from this.

Thanks!

**AB33** · 05-31-2013, 11:41 AM

Added comment as per requested.

**Groovo** · 05-31-2013, 07:58 PM

AB33,

The macro works great, and I thank you for the notes on how it works. I needed to add a replace " " with ">" to the code, and I was able to Google it. here is the revised code that does exactly what I need.

Please Login or Register  to view this content.

Thank you again for your great help!

Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

LinkBack

Thread Tools

Rate This Thread

Display

Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Re: Identify (iserror) in cell B1, Copy value E1 into A2, and delete row 1.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions